Question 136793 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Prevent WHERE from matching on same row

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!

Tags:

2

Answers


  1. 0

    Add another condition to the second JOIN that it be a different officer.

    SELECT DISTINCT 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 AND aBoff.id != bBoff.id
WHERE  aBoff.profession = :aprofession AND  bBoff.rank = :brank
    Login or Signup to reply.
  2. 0

    Add a condition to the join condition to prevent same-row matches:

    LEFT JOIN bridge_officers AS bBoff ON a.id = bBoff.ship_id
    AND aBoff.id < bBoff.id

    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 your where clause

    WHERE aBoff.profession = :aprofession
AND bBoff.rank = :brank

    checks 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:

    WHERE aBoff.profession = :aprofession
AND aBoff.rank = :brank -- aBoff.rank, not bBoff.rank

    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 the where clause to be true, so the LEFT keyword is meaningless here and should be removed for clarity:

    SELECT DISTINCT a.id
FROM ships AS a
JOIN bridge_officers AS aBoff ON a.id = aBoff.ship_id
JOIN bridge_officers AS bBoff ON a.id = bBoff.ship_id
  AND aBoff.id < bBoff.id
WHERE aBoff.profession = :aprofession
AND aBoff.rank = :brank
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search