CODEWITHSUNDEEP
postgresql
zumzum
zumzum

Reputation: 20228

Postgres, select all records from table A and left join with possible existing records of B on condition?

I have a device table, and a user_device_details table. There can be multiple user_device_details for one device record.

I want to get a few devices (usually around 3) by device.id, and left join possible existing user_device_details records with it for a given user_device_details.user_id

So, give me all devices with id (23,4,10) and if you find a related user_device_details with user_id=87 join it so I can have the device and the possible user_device_details record generated by user 87.

I am not sure how I can return all three devices even if maybe only device 23 has a user_device_details record.

So, ALWAYS return all devices I pass ids in for, and if it has user_device_details from a specific user_id then also put that next to the device row result (join?).

I tried something like this:

select *
from device d
left join device_details dd on d.id = dd.device_id
where d.id in (23,4,10)
and dd.user_id=${userId}

When I run this, I only get device 23 because it's the only one that has user_device_details.user_id=87.

How do I get all three devices together with the two null user_device_details for devices 4 and 10?

Upvotes: 1

Views: 899

Answers (1)

user330315
user330315

Reputation:

The condition where.dd_user_id = .. turns your left join into an inner join as it will remove all rows that did not have a match for the join condition.

You probably want:

select *
from device d
  left join device_details dd 
         on d.id = dd.device_id
        and dd.user_id=${userId}
where d.id in (23,4,10)

Upvotes: 3

Related Questions