SQL JOIN 2 conditions

Question

I have two tables in my mysql database. table1: shoutbox table2: users

My tables look like this

TABLE SHOUTBOX (id, name, title, user_id)

TABLE USERS (id, name, profile_image)

What i would like to do is display everything from table SHOUTBOX but condition must be where profile_image = '2' in USERS table So i've tried this:

SELECT shoutbox.id, shoutbox.name, shoutbox.title, shoutbox.user_id, user.profile_image
FROM shoutbox, users
WHERE users.profile_image = '2'

and i'm getting very wrong results. So i'm total newbie at this so please help out =)

Answer 1

you are getting the cartesian product from two tables because you lack the very important condition that links both tables. Add this condition on the WHERE clause.

SELECT ... FROM ....
WHERE users.profile_image = '2' AND
      shoutbox.user_ID = users.id

but i urged you to use SQL-92 syntax

SELECT   shoutbox.id, 
         shoutbox.name,
         shoutbox.title, 
         shoutbox.user_id, 
         users.profile_image
FROM     shoutbox INNER JOIN users ON shoutbox.user_ID = users.id
WHERE    users.profile_image = '2'

• Visual Representation of SQL Joins

Answer 2

SELECT shoutbox.id, shoutbox.name, shoutbox.title, 
shoutbox.user_id, user.profile_image
FROM shoutbox
inner join users
on user_id= users.id
WHERE users.profile_image = '2'

if you are newbie than get info about joins

Already answered question : What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Answer 3

SELECT shoutbox.id, 
       shoutbox.name, 
       shoutbox.title, 
       shoutbox.user_id, 
       user.profile_image
FROM shoutbox
join users
on shoutbox.user_id=users.id
WHERE users.profile_image = '2'