CODEWITHSUNDEEP
sqlsql-serverdatabaseinner-join
j.Doe
j.Doe

Reputation: 172

JOIN the table if records exist

is it possible if i want to do INNER JOIN only if the record exist on the 2nd table if not then dont join?

this is my table

User table

+--------+--------------+
|   id   |     name     | 
+--------+--------------+
|   1    | John         |
+--------+--------------+
|   2    | Josh         | 
+--------+--------------+

House table

+--------+-------------+--------------+ 
|   id   |   owner_id  |   house_no   |
+--------+-------------+--------------+
|   1    |      1      |      991     | 
+--------+-------------+--------------+

this is my INNER JOIN query

SELECT h.owner_id, u.name, h.house_no FROM user u
INNER JOIN house h on u.id = h.owner_id 
WHERE u.id = :id

it will return this result if id = 1

+--------+--------------+--------------+ 
|   id   |     name     |   house_no   |
+--------+--------------+--------------+
|   1    | John         |      991     | 
+--------+--------------+--------------+

but if i run with id = 2 no result returned.

what i want to do right now is it still return the result even when no data exist for id = 2 in table house

Upvotes: 0

Views: 59

Answers (1)

Mason T.
Mason T.

Reputation: 1577

Use a left outer join instead.

SELECT u.id, u.name, h.house_no FROM user u
LEFT OUTER JOIN house h on u.id = h.owner_id 
WHERE u.id = :id

The resulting record will be:

+--------+--------------+--------------+ 
|   id   |     name     |   house_no   |
+--------+--------------+--------------+
|   2    |     Josh     |     null     | 
+--------+--------------+--------------+

Upvotes: 1

Related Questions