Wrong data output on basic select condition

i’m trying to get the values which Dates bigger then 2021-07-17 and Cities starting with ‘G’. I have id in the PERSON table, id (references from PERSON(id)) and date in VACCDATA table, id (references from PERSON(id)) and city in ADDRESS table.

SELECT p.id, v.vac_date, a.city
FROM PERSON AS p
LEFT JOIN VACCDATA AS v
ON v.vac_date > '2021-07-17' AND v.id=p.id
LEFT JOIN ADDRESS AS a
ON a.city LIKE 'G%' AND a.id=v.id;


  

          ID       DATE        CITY

Row 1 --> 15 ; "2021-07-19" ; ""

Row 2 --> 18 ; "2021-07-18" ; "Giresun"

Row 3 --> 17 ; "" ; ""

While i only want the Row 2 (because it’s city name is starts with ‘G’ and Date is bigger then 2021-07-17 at the same time), it gives the above output. What am i doing wrong?

Answer

You are using LEFT JOIN with the conditions on the join itself. So you get all persons from database no matter the criteria is matching.

You can use INNER JOIN with WHERE filter instead:

SELECT p.id, v.vac_date, a.city
FROM PERSON AS p
  INNER JOIN VACCDATA AS v ON v.id = p.id
  INNER JOIN ADDRESS AS a ON a.id = v.id
WHERE v.vac_date > '2021-07-17' AND a.city LIKE 'G%'