LEFT JOIN does not return EXPECTED RESULT-CodePudding

I wrote a query to update the imageSrc of an object on database : UPDATE BOXES SET BOXES.IMAGESRC = ?1 WHERE BOXES.ID = ?2 , and it actually works, because I checked on the database my table Boxes and it updated correctly the imageSrc of the Box with ID=1. (Result here)

My problem comes when I execute the following LEFT JOIN query SELECT * FROM BOXES AS A LEFT JOIN MATCHES_BOXES AS B ON A.ID=B.MATCH_ID WHERE B.MATCH_ID LIKE ?1 Because it gives me the Box with ID=1 with is OLD imageSrc (Result of LEFT JOIN)

As you can see, the BOX with ID=1 does not cointains the correct imageSrc (/resources/images/cards/blue-01.png), instead, it cointains the old imageSrc (/resources/images/celda.png)

And it does not make sense to me, because I LEFT JOINED with the table Boxes, where the Box with ID=1 already have the correct imageSrc, but returns the old.

CodePudding user response：

This happens as your where clause on the left join converts the query into an inner join.

   SELECT * 
     FROM BOXES AS A 
LEFT JOIN MATCHES_BOXES AS B 
       ON A.ID=B.MATCH_ID 
    WHERE B.MATCH_ID LIKE ?1

Analyze the scenarion, if you wanted to get recrods in Boxes regardless of whether it was present in MATCHES_BOXES then the resulting rows from B will all have been NULL for the missing MATCH_ID in B(ie no_row_exists).

But since you got a WHERE B.MATCH_ID LIKE '?1' condition after the JOIN, it will negate those missing MATCH_ID as you are basically check WHERE NULL like '?1' and since NULL cannot get equated with anything, the missing recrods are not retreived in the query.

If your intention is to get all records from BOXES even if they dont exist in MATCHES_BOXES, and also those records from BOXES which exist in MATCHES_BOXES on the ID and you wish to bring them only if MATCH_ID LIKE '?1', the you would JOIN on the AND condition as follows

   SELECT * 
     FROM BOXES AS A 
LEFT JOIN MATCHES_BOXES AS B 
       ON A.ID=B.MATCH_ID 
      AND B.MATCH_ID LIKE ?1 /*use AND instead of WHERE*/

CodePudding user response：

The outer join doesn't seem to make much sense. Anyway, you are selecting MATCHES_BOXES looking for a particular MATCH_ID (3 in your sample result). You join the BOXES table on the same column. Thus you are showing BOXES with the ID 3. MATCHES_BOXES also has a column BOXES_ID. This has nothing to do with the joined BOXES row, however. When you see the IMAGESRC for BOXES row for ID 3, it is because you are joining this row.

I suppose you simply want to join on BOXES_ID instead:

SELECT * 
FROM boxes b
JOIN matches_boxes mb ON mb.boxes_id = b.id
WHERE mb.match_id = ?1;