MySQL AND alternative for each table in a join
- by Scott
I have a simple join in a query however I need to have a condition on both of the tables "confirmed='yes'" but if one of the tables doesn't have any that match the query returns with no rows.
Database:
.----------parties----------.
| id - party_id - confirmed |
|---------------------------|
| 1 1 yes |
| 1 2 no |
| 1 3 no |
+---------------------------+
.-----------events----------.
| id - event_id - confirmed |
|---------------------------|
| 1 1 no |
+---------------------------+
Query:
SELECT p.party_id, e.event_id
FROM parties p
LEFT JOIN events e
ON p.id=e.id
WHERE p.id = '1'
AND p.party_id IN (1,2,3)
AND e.event_id IN (1)
AND p.confirmed='yes'
AND e.confirmed='yes'
It returns nothing but I want it to return party_id 1 with a empty event_id. I hope this make sense and I not missing anything, Thanks for your help!
