Search code examples
mysqlsqlcorrelated-subquery

Query with subquery not returning a value that should be returned

Why this query

SELECT *
FROM tbl X
WHERE NOT EXISTS (SELECT * FROM tbl Y WHERE X.end_date=Y.start_date)
ORDER BY accommodation_id, start_date

does not return the following row?

enter image description here

I verified the row is not returned adding AND accommodation_id = 7049 to the WHERE clause, so the resulting query is:

SELECT * FROM wp_byt_accommodation_vacancies X WHERE NOT EXISTS(SELECT * FROM wp_byt_accommodation_vacancies Y WHERE X.end_date=Y.start_date) AND accommodation_id = 7049 ORDER BY accommodation_id, start_date

I verified there aren't rows with start_date = '2017-04-08 for that accommodation_id using the following query:

SELECT * FROM wp_byt_accommodation_vacancies WHERE accommodation_id = 7049 AND start_date = '2017-04-07'
Solution

  • You should use following query

    SELECT *
FROM tbl X
WHERE NOT EXISTS (  SELECT * 
                    FROM tbl Y 
                    WHERE X.end_date=Y.start_date
                    AND X.accommodation_id = Y.accommodation_id)
ORDER BY accommodation_id, start_date

    You have forgot to link the key X.accommodation_id = Y.accommodation_id