TalkPHP - JOIN query

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - JOIN query (http://www.talkphp.com/mysql-databases/5640-join-query.html)

captainmerton

11-24-2010 07:46 PM

JOIN query

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.

All times are GMT. The time now is 09:36 AM.