JOIN query

captainmerton · 11-24-2010, 07:46 PM

I'm trying join 2 tables and think I require a LEFT join as all rows in table1 are required but struggling to handle the rules associated with table2. Looking for some help/guidance. My tables are basically:

TABLE1
userid. user
1. Mr Smith
2. Mr Davis
3. Mrs Jackson

TABLE2
imageid. Main_image. userid
23. Y 1
24. N. 1
25. Y. 2

I'm looking for a query that will return every row in table1 then rows associated in table2 assuming the main_image flag is set to Y.

I've done a left join but when append AND main_image = Y i lose rows from table1 that don't match that criteria I.e. User 3. How can I pull all users from table1 then pull in all main_images from table2?

Hope u get the gist having trouble easily documenting this as typing it on an iPhone but any help greatly appreciated.

Schroder · 11-24-2010, 10:50 PM

I think you need to add an "OR imageid IS NULL" so if there's no associated record in the second table, the user record will still be returned.

maeltar · 11-25-2010, 07:09 AM

something like

(The associated record was userid)

Code:

select * from table1 left join table2 on table1.userid = table2.userid


+--------+-----------+-------+--------+--------+
| userid | name      | imgid | img    | userid |
+--------+-----------+-------+--------+--------+
|      1 | Mr Smith  |     3 | image3 |      1 |
|      2 | Mr Jones  |     1 | image1 |      2 |
|      2 | Mr Jones  |     2 | image2 |      2 |
|      3 | Mrs Jones |  NULL | NULL   |   NULL |
|      4 | Mr Patel  |     4 | image4 |      4 |
|      4 | Mr Patel  |     5 | image5 |      4 |
+--------+-----------+-------+--------+--------+

Another option is :

Code:

 select * from table1, table2 where table1.userid = table2.userid;
+--------+----------+-------+--------+--------+
| userid | name     | imgid | img    | userid |
+--------+----------+-------+--------+--------+
|      1 | Mr Smith |     3 | image3 |      1 |
|      2 | Mr Jones |     1 | image1 |      2 |
|      2 | Mr Jones |     2 | image2 |      2 |
|      4 | Mr Patel |     4 | image4 |      4 |
|      4 | Mr Patel |     5 | image5 |      4 |
+--------+----------+-------+--------+--------+

captainmerton · 11-26-2010, 12:42 PM

Schroder thanks your solution solved it. In my head i knew what i wanted to do but had the following:

OR imageid = NULL

which didnt bring back the rows where the image columns were null. Changed that to:

OR imageid IS NULL

and it worked.

Cheers.

Dr John · 12-31-2010, 05:38 PM

this bit OR imageid = NULL
fails because no value can be equal to null. (it's an sql rule)
The IS NULL gets round this problem.

you can also use ifnull( table1.imageid, 0 ) AS imageID to substitute 0 (or even 'unknown') for null, which looks better to users who don't understand null.

abbot · 12-06-2012, 11:29 AM

good practice.