SQL Query - Select Records from multiple tables and fetch data

Question

Dear Team,

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

1.

2.

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%

3.

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....

Sachin Singh · 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 Singh · Answer

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 Valecha · Answer

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 ??