Click here to Skip to main content
15,942,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello
how can i get data from 2 tables even if there is no data into other table
ex: fathers table has columns father_id,name .. sons table has columns son_id ,name

this query
select f.father_id,f.name,s.name from fathers f,sons s
where f.father_id=s.father_id and father_id=@parameter

i want to get data from these tables if exists related father_id into son table and if not get it as null or any empty string

like this if no data into son table
father_id,fathername ,sonname
1 templ null
2 john null

thanks all
Posted
Updated 24-Dec-11 0:17am
v3

Use outer join to get records if there's no matching record in another table. See: http://msdn.microsoft.com/en-us/library/ms187518.aspx[^]

So in you statement, something like:
SQL
select f.father_id,f.name,s.name 
from fathers f left outer join sons s
on f.father_id=s.father_id


EDIT:
If you want to restrict using father_id, something like:
SQL
SELECT f.father_id,
       f.name,
       s.name
FROM  fathers f LEFT OUTER JOIN sons s
ON    f.father_id = s.father_id
WHERE f.father_id = @father_id 


That should bring a single father with all sons, if any exist
 
Share this answer
 
v2
Comments
Abhinav S 24-Dec-11 6:19am    
My 5. Same answer as mine. :)
Wendelius 24-Dec-11 6:55am    
Thanks :)
Amir Mahfoozi 24-Dec-11 6:34am    
+5
Wendelius 24-Dec-11 6:55am    
Thanks :)
mohammed sadeq 25-Dec-11 4:49am    
thanks all
but left join doesn't work with where clause ex: i want to filter it with where father_id=@father_id so it not work
Use left outer join to build your query.
 
Share this answer
 
Comments
Amir Mahfoozi 24-Dec-11 6:35am    
+5 Your answer is elegant for this forum which is "Quick Answers"
Abhinav S 24-Dec-11 7:32am    
Thank you.
Wendelius 24-Dec-11 6:55am    
Indeed, 5
Abhinav S 24-Dec-11 7:32am    
Thank you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900