SQL Query - Select Records from multiple tables and fetch data

Riddhi Valecha

5y888SQL

Dear Team,

I have 3 tables as below - (Candidate ID column is unique and is used as foreign key)

Table - TBLEducationDetails
ID	CandidateID	Type	Degree	Year	Institute	Subject	Marks
1	101A	Full Time	MBA+IT	2011	IIT Bombay	Management,Computers	60%
2	101A	Full Time	12th	2010	Mumbai	HSBC	76%
3	102B	Part Time	M.COM+IT	2012	IIT Madras	Management,Computers	65%
4	102B	Full Time	12th	2010	Mumbai	HSBC	68%
5	103C	Distance Learning	MBA	2011	IIT Kanpur	Management,Computers	70%

Table - TBLCurrentOrganizationDetails
ID	CandidateID	Org Name	From Date	ToDate	Job	Location
1	101A	ABC Pvt Ltd	01-01-2013	01-01-2015	Java Developer	Mumbai
2	102B	PQR Pvt Ltd	02-01-2013	02-01-2015	Oracle	Chennai
3	103C	GHI Pvt Ltd	03-01-2013	03-01-2015	Manager	Kolkata
4	103C	JKL Pvt Ltd	03-01-2015	03-01-2016	Sr. Manager	Kolkata

Now, I want to fetch all the data of all the candidates in the below format -

How do I get this report form in SQL Query?

Or Is there any other way to get data and display it in grid view?

Please help in this....

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
simply join all the tables based on CandidateId
select *
from  TBLPersonalDetails PD
inner join  TBLEducationDetails ED
on PD.CandidateId=ED.CandidateId
inner join  TBLCurrentOrganizationDetails OD
on ED.CandidateId =OD.CandidateId
Sachin Singh5y ago
if you have to show one by one like first personal details then educational, then you need three select queries as one command and then loop throw using Reader.Next()
using(SqlConnection con=new SqlConnection(cs))
{

SqlCommand cmd= new SqlCommand("select * from personalDetails;select * from EducationDetails;select * from CurrentOrganizationalDetails",con);
con.Open();


          using (SqlDataReader reader = command.ExecuteReader())
    {
        List pdList=new  List();
       pdList.Add(new PeronalDetails(){
Name= reader[2].ToString();
// so on
})

        while (reader.NextResult())
        {

        List EDList=new  List();
       EDList.Add(new EducationaDetail(){
Type= reader[2].ToString();
// so on
})
        }
while (reader.NextResult())
        {

        List ODList=new  List();
       ODList.Add(new CurrentOrganizationalDetail(){
OrgName= reader[2].ToString();
// so on
})
        }
    }
now in view, you can loop through each list.
There are multiple approaches, join can also work but as per the requirement, separate result sets are easy.
Riddhi Valecha5y ago
Hello Sachin Sir,

Thanks a ton for your help in SQL Query.... Actually, I am a bit weak in this area.

I have one more query - How will I display it in the format given ?

Give HEading -
Personal Details-
Then First row
Next HEading - Education - One loop for it
??