Help with MySQL Query?

Question

I have two tables rooms and users. I want to get only rooms.room_id, users.user_name with user_id = 1. I can get the result of all users with following sql...

select rooms.room_id,
       rooms.user_id,
       users.user_name 
  from rooms 
LEFT JOIN users ON rooms.user_id = users.user_id

When I do like this to filter the result with user_id = 1 ... I got error.

select rooms.room_id,
       rooms.user_id,
       users.user_name 
  from rooms where rooms.user_id = 1 
LEFT JOIN users ON rooms.user_id = users.user_id

What should I do?

Answer 1

ANSI-92 JOIN syntax (when you see LEFT JOIN ...) dictates that the WHERE clause comes after the JOIN(s):

   SELECT r.room_id,
          r.user_id,
          u.user_name 
     FROM ROOMS r 
LEFT JOIN users ON u.user_id = r.user_id
    WHERE r.user_id = 1 

You were close.

Answer 2

Try:

select rooms.room_id,
       rooms.user_id,
       users.user_name 
from rooms
LEFT JOIN users ON rooms.user_id = users.user_id
WHERE rooms.user_id = 1 

The syntax of a simple SQL SELECT query is:

SELECT [a list of fields]
FROM [a single table name maybe with an alias, or a join of tables]
WHERE [a filter, applied over some fields of the tables in the FROM clause]

You could read an introductory tutorial here.

Answer 3

It helps to state what error you get.

I would guess that the problem is that the where clause needs to be after the joins

select rooms.room_id,
   rooms.user_id,
   users.user_name 
from rooms 
  LEFT JOIN users ON rooms.user_id = users.user_id
where rooms.user_id = 1 

Answer 4

Write the query as:

select rooms.room_id,
       rooms.user_id,
       users.user_name 
  from rooms 
LEFT JOIN users ON rooms.user_id = users.user_id
WHERE roows.user_id = 1