I have a database with two tables, ships
and officers
. A ship
has multiple officers
. To search for a ship
with a specific officer
, I have a query like this:
SELECT a.id
FROM ships AS a
LEFT JOIN bridge_officers AS aBoff ON a.id = aBoff.ship_id
WHERE aBoff.profession = :aprofession
GROUP BY a.id
This will result in all ships
with an officer
that has :aprofession
. The same JOIN
/WHERE
is repeated to filter by more than one officer
.
For example:
SELECT a.id
FROM ships AS a
LEFT JOIN bridge_officers AS aBoff ON a.id = aBoff.ship_id
LEFT JOIN bridge_officers AS bBoff ON a.id = bBoff.ship_id
WHERE aBoff.profession = :aprofession AND bBoff.rank = :brank
GROUP BY a.id
This will result in all ships
with that has an :aprofession
and :brank
officers
. My desired behavior is that only ships with two difference officers
matching these criteria are returned. If a single officer
has both :aproffesion
and :brank
, it’ll return that ship. I’d prefer if each WHERE
condition would only match to one join.
Is there anyway to accomplish that? Thanks!
2
Answers
Add another condition to the second JOIN that it be a different officer.
Add a condition to the join condition to prevent same-row matches:
chosing
<
rather than!=
to halve the intermediate result set; only producing unique combinations of any two officers.Also, there seems to be a bug in the
where
clause – your question states a single officer has both :aproffesion and :brank, but yourwhere
clausechecks if the officer as a certain profession and the other officer has a certain rank – it seems the conditions should be applied to the same joined table
aBoff
:There’s also another subtle issue with your query: by applying a
where
clause to your outer joined tables you convert them to inner joins, because the joined row must exist (and be joined to) for thewhere
clause to be true, so theLEFT
keyword is meaningless here and should be removed for clarity: