CODEWITHSUNDEEP
mysqlsql
Greg3030
Greg3030

Reputation: 81

Select on 2 tables return no result?

I have 2 tables :

Table 'annonce' (real estate ads) :

idAnnonce  |  reference
-----------------------
1          |  dupond
2          |  toto

Table 'freeDays' (Free days for all ads) :

idAnnonce  |  date
-----------------------
1          |  2015-06-06
1          |  2015-06-07
1          |  2015-06-09
1          |  2015-06-10
2          |  2015-06-06
2          |  2015-06-07
2          |  2015-06-12
2          |  2015-06-13

I want to select all alvailable ads who have only free days between a start and end date, I have to check each days between this date.

The request : 

    SELECT DISTINCT
        `annonce`.`idAnnonce`, `annonce`.`reference` 
    FROM
        `annonce`, `freeDays` 
    WHERE
        `annonce`.`idAnnonce` = `freeDays`.`idAnnonce`
    AND
        `freeDays`.`date` = '2015-06-06'
    AND
        `freeDays`.`date` = '2015-06-07'

Return no result. Where is my error ?

Upvotes: 2

Views: 34

Answers (3)

James Harcourt
James Harcourt

Reputation: 6379

Your WHERE clause is asking for the impossible!

You are asking for rows where the 'freedays'.'date' value is both 2015-06-06 and 2015-06-07.

AND
    freeDays.date = '2015-06-06'
AND
    freeDays.date = '2015-06-07'

You need to use BETWEEN:

freeDays.date BETWEEN '2015-06-06' AND '2015-06-07'

AND
    freeDays.date = '2015-06-06'
OR
    freeDays.date = '2015-06-07'

Upvotes: 0

Arion
Arion

Reputation: 31239

What Matt is say is correct. You can also do this as alternative:

SELECT DISTINCT a.idAnnonce, a.reference 
FROM annonce a 
INNER JOIN freeDays f ON a.idAnnonce = f.idAnnonce
WHERE f.date IN('2015-06-06','2015-06-07')

Or like this:

SELECT DISTINCT a.idAnnonce, a.reference 
FROM annonce a 
INNER JOIN freeDays f ON a.idAnnonce = f.idAnnonce
WHERE f.date ='2015-06-06' OR f.date ='2015-06-07'

This will give you the same result as with an BETWEEN

Upvotes: 1

Matt
Matt

Reputation: 15061

It cant be equal both dates

SELECT DISTINCT a.idAnnonce, a.reference 
FROM annonce a 
INNER JOIN freeDays f ON a.idAnnonce = f.idAnnonce
WHERE f.date BETWEEN '2015-06-06' AND '2015-06-07'

Upvotes: 3

Related Questions