Retrieve multiple rows from table2 based on single row from table1 in SQL Server I have two tables and I am using SQL Server.

table1 structure is

Campiagn_Id  Campiagn_Name 
1            test1
2            test2
3            test3


and table2 structure is 

Campign_Id  EmalList
1           email_list_1
1           email_list_2
3           email_list_3
1           email_list_4
2           email_list_5
2           email_list_6
3           email_list_7
1           email_list_8


Now I want to fetch all the Campiagn_Id and Campiagn_Name from table1 and also all the respective EmalList from table2 in one query.

My Result will look like this

Campign_Id   Campiagn_Name    EmailList 
1            test1            email_list_1,email_list_2,email_list_4,email_list_8
2            test2            email_list_5,email_list_6
3            test3            email_list_3,email_list_7


Or any other suggested output will be fine

I think my question is pretty much clear to all of you.
The query you need is:

SELECT 
    camp.Campiagn_Id, 
    camp.Campiagn_Name, 
    camp_list.Campign_Id, 
    camp_list.EmalList 
FROM 
    [table1] AS camp 
    LEFT JOIN [table2] AS camp_list ON (camp.Campiagn_Id = camp_list.Campiagn_Id);


This will show all campaigns with all emails lists for each campaign. It will show also campaign with no emails list.

You can add or remove fields in the select as you need.