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:
1. Mr Smith
2. Mr Davis
3. Mrs Jackson
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.
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.
(The associated record was userid)
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.
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.
|All times are GMT. The time now is 01:46 AM.|
Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0