Query 2 tables with one field linked to 2 different values

Question

I'm trying to make a SQL query and I have some problems with it.

CREATE table entries (
id_entry INT PRIMARY KEY,  
);

CREATE table entry_date (
  entry_date_id INT PRIMARY KEY,
  entry_id INT,
  entry_price INT,
  entry_date TEXT,
);

for each entry, there is several dates.

I'd like to select the entries.entry_id where that entry have, for example, the dates '23/03/2013' and '24/03/2013' linked to it.

The two dates are stored into an array:

 $data = array('ci' => '23/03/2013', 'co' => '24/03/2013');

I store the dates in text for practical purpose in my treatment. I use Zend_Db so my query is constructed like that:

$select = $table->select ()->from ( 'entries' )->setIntegrityCheck ( false );

        if ($data ['ci'] != NULL) {
            $select->join ( array (
                    'entry_dates' => 'entry_dates' 
            ), 'entries.id_entry = entry_dates.entry_id' );
            $select->where ( 'entry_dates.entry_date = ?', $data ['ci'] );
        }
        if ($data ['co']) {
            if ($data['ci'] == NULL) {
            $select->join ( array (
                    'entry_dates' => 'entry_dates' 
            ), 'entries.id_entry = entry_dates.entry_id' );}
            $select->where ( 'entry_dates.entry_date = ?', $data ['co'] );  
        }

which gives :

SELECT `entries`.*, `entry_date`.* 
FROM `entries` 
INNER JOIN `entry_dates` 
ON entries.id_entry = entry_dates.entry_id 
WHERE (entry_dates.entry_date = '23/03/2013') 
AND (entry_dates.entry_date = '24/03/2013')

And, well ... It doesn't work. When I fetch my $select, I get nothing.

I guess I miss something in my request when I do the WHERE ... AND , what should I do to get the correct output ? The real request being really long, I'd like to avoid another long subselect if possible.

Answer 1

It can be done in two way, either with a self-join on the entry_date table:

SELECT `entries`.entry_id
FROM `entries` 
INNER JOIN `entry_dates` AS ed1
ON entries.id_entry = ed1.entry_id 
INNER JOIN `entry_dates` AS ed2
ON entries.id_entry = ed2.entry_id 
WHERE ed1.entry_date = '23/03/2013'
AND ed2.entry_date = '24/03/2013'

Or with an aggregate

SELECT `entries`.entry_id
FROM `entries` 
INNER JOIN `entry_dates` AS ed
WHERE ed.entry_date = '23/03/2013'
OR ed2.entry_date = '24/03/2013'
GROUP BY `entries`.entry_id
HAVING COUNT(DISTINCT ed.entry_date)=2