sql query using left joins and where clause

Question

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

Just to clarify: 1) eIMG is the table that contains the picture, and 2) not all events have a picture? If you want to join to a table that may not contain a row that matches a join, you need to use an OUTER join instead. — Alex
– Alex, Commented Feb 27, 2014 at 18:07
@GoatCO sorry - I tend to explicitly specify inner vs outer so I was off track. — Alex
– Alex, Commented Feb 27, 2014 at 18:23

Hart CO · Accepted Answer · 2014-02-27 18:00:11Z

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.

Manu · Accepted Answer · 2014-02-27 18:00:09Z

1

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

answered Feb 27, 2014 at 18:00

Manu

9111 gold badge8 silver badges29 bronze badges

Collectives™ on Stack Overflow

sql query using left joins and where clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related