Select data from multiple tables even if there no data in one table

Question

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 24-Dec-11 0:14am

mohammed sadeq

Updated 24-Dec-11 0:17am

v3

Add a Solution

2 solutions

Solution 2

Use left outer join to build your query.

Posted 24-Dec-11 0:19am

Abhinav S

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.

Add a Solution

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

Wendelius · Accepted Answer · 2011-12-24T00:17:00

Solution 1

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

Posted 24-Dec-11 0:17am

Wendelius

Updated 24-Dec-11 23:21pm

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

Wendelius 25-Dec-11 5:21am

Answer updated.

Select data from multiple tables even if there no data in one table

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Select data from multiple tables even if there no data in one table

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us