CODEWITHSUNDEEP
mysqlsqlselectleft-join
user2886669
user2886669

Reputation: 251

sql query using left joins and where clause

I have the following query

SELECT *
  FROM tbl_events AS event
       LEFT JOIN tbl_business_events AS bevent 
                 ON event.event_id = bevent.event_id 
       LEFT JOIN tbl_business_sector AS bsec 
                 ON event.event_id = bsec.event_id 
       LEFT JOIN tbl_sectors AS sector 
                 ON bsec.sector_id = sector.sector_id 
       LEFT JOIN tbl_event_location AS eloc 
                 ON event.event_id = eloc.event_id 
       LEFT JOIN tbl_locations AS location 
                 ON location.location_id = eloc.location_id 
 WHERE event.event_type = 1

which i use to pull out all the details of my event listings, this works but i now want to pull out the events profile picture from another table tbl_event_images. This table stores all the events images but has a field called is_profile_picture which determins if it is the profile pic or not (value is 0 for not and 1 if the image is the profile pic)

so i tried the following query to grab the event profile picture as well as the rest of the information with the following query

SELECT *
  FROM tbl_events AS event
       LEFT JOIN tbl_event_images AS eIMG 
                 ON event.event_id = eIMG.event_id
       LEFT JOIN tbl_business_events AS bevent 
                 ON event.event_id = bevent.event_id
       LEFT JOIN tbl_business_sector AS bsec 
                 ON event.event_id = bsec.event_id
       LEFT JOIN tbl_sectors AS sector 
                 ON bsec.sector_id = sector.sector_id
       LEFT JOIN tbl_event_location AS eloc 
                 ON event.event_id = eloc.event_id
       LEFT JOIN tbl_locations AS location 
                 ON location.location_id = eloc.location_id
 WHERE event.event_type =1

but this query returns a new row for each photo the event has in that table.

so i then tried to add another where clause to the above AND eIMG.is_profile_picture =1

but because not every listing has photos let alone a profile picture the above query only returns the events that have a profile picture.

how can i return 1 row for each event regardless if they have a profile pic or not? if they do then it will add the name of the image if it doesnt it sets it to NULL

Thanks Luke

Upvotes: 1

Views: 86

Answers (2)

Hart CO
Hart CO

Reputation: 34774

Instead of using WHERE criteria add the is_profile_picture condition to your JOIN criteria:

SELECT *
FROM tbl_events AS event
LEFT JOIN tbl_event_images AS eIMG 
   ON   event.event_id = eIMG.event_id 
    AND eIMG.is_profile_picture =1
LEFT JOIN tbl_business_events AS bevent 
   ON event.event_id = bevent.event_id
LEFT JOIN tbl_business_sector AS bsec 
   ON event.event_id = bsec.event_id
LEFT JOIN tbl_sectors AS sector 
   ON bsec.sector_id = sector.sector_id
LEFT JOIN tbl_event_location AS eloc 
   ON event.event_id = eloc.event_id
LEFT JOIN tbl_locations AS location 
   ON location.location_id = eloc.location_id
WHERE event.event_type =1

WHERE filters all results, and in this case effectively negates your use of LEFT JOIN. Adding to the JOIN criteria itself means only some pictures will join, but all the results of your original query will still be returned.

Upvotes: 1

Manu
Manu

Reputation: 901

Try this

SELECT *
FROM tbl_events AS event
LEFT JOIN tbl_event_images AS eIMG ( ON event.event_id = eIMG.event_id AND  eIMG.is_profile_picture = 1 )
LEFT JOIN tbl_business_events AS bevent ON event.event_id = bevent.event_id
LEFT JOIN tbl_business_sector AS bsec ON event.event_id = bsec.event_id
LEFT JOIN tbl_sectors AS sector ON bsec.sector_id = sector.sector_id
LEFT JOIN tbl_event_location AS eloc ON event.event_id = eloc.event_id
LEFT JOIN tbl_locations AS location ON location.location_id = eloc.location_id
WHERE event.event_type =1

Upvotes: 1

Related Questions