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:
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,, 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
Updated 24-Dec-11 0:17am

Use outer join to get records if there's no matching record in another table. See:[^]

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

If you want to restrict using father_id, something like:
SELECT f.father_id,,
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
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    
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
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