0

I need to get a list of users with their corresponding bed_id in case user has bed_id and fill with null if user doesn't have bed_id since there will always been more users than beds.

When I try this:

select users.*, beds.bed_id from users, beds where users.user_id = beds.user_id;

I got a list of users who have assigned bed_id alone but I need the complete list of users with their bed_id or null.

USERS
--------------
| user_id    |
| name       |
| field1     |
| field2     |
| ...        |
--------------

BEDS
--------------
| bed_id     |
| user_id    |
| room_id    |
| field1     |
| ...        |
--------------

Thanks for the great support you always share here.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Woodsoul
  • 155
  • 1
  • 10
  • 3
    you need to use left join – eshirvana May 14 '22 at 17:25
  • 1
    Thanks for your good explanation, most questions are much less detailed. Since it was already mentioned a LEFT or even LEFT OUTER JOIN will do, I just want to say it's generally recommended to do not use comma-"JOINING" tables, but to always use JOIN, LEFT JOIN etc. If you want to know why, this is a good option: https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause – Jonas Metzler May 14 '22 at 17:29
  • 2
    MySQL or Oracle? – Salman A May 14 '22 at 17:33
  • 1
    @JonasMetzler: as you're probably aware, `LEFT JOIN` and `LEFT OUTER JOIN` mean the same thing. – Bob Jarvis - Слава Україні May 14 '22 at 18:32
  • @SalmanA Hello. It is an Oracle DB. MT0s answer works as expected for now. Is there any different on doing a left outer join between MySQL and Oracle? Thanks. – Woodsoul May 15 '22 at 12:11

1 Answers1

4

Use LEFT OUTER JOIN:

select users.*,
       beds.bed_id
from   users
       LEFT OUTER JOIN beds
       ON (users.user_id = beds.user_id);
MT0
  • 143,790
  • 11
  • 59
  • 117