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.

`(entry_dates.entry_date = '23/03/2013') AND (entry_dates.entry_date = '24/03/2013')`, so you want the date to be equal to 23/03/2013 and equal to 24/03/2013 at the same time ? Also I'm pretty sure MySQL doesn't like date literals in that format. — Vatev, Mar 23 '13 at 18:13
I'm aware of this but how can I specify that I want that these 2 dates come from 2 different entry_date ? I'd like to get the entry that has 2 entry_dates being '23/03/2013' and the other one '24/03/2013' — Jay Zus, Mar 23 '13 at 18:31
Do you want to select only entries.entry_id or also the data from entry_date table? — Jakub Kania, Mar 23 '13 at 19:02

score 1 · Accepted Answer · answered Mar 23 '13 at 19:09

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

answered Mar 23 '13 at 19:09

Jakub Kania

15,665
2
37
47

Thanks, it worked fine ! Do you know which one will be the fastest when the database will contain thousands of entries ? – Jay Zus Mar 27 '13 at 14:52
@JayZus My guess would be that first one might be faster but I never compared such queries really so it's just a guess. If you test them please let me know which one performs beter. – Jakub Kania Mar 27 '13 at 15:36

Query 2 tables with one field linked to 2 different values

1 Answers1