MySQL query: Get records thats have records in a all the date range.

Hi,

I've two tables:

Table Items:

id        -             name
----------------------------------
1         -        example 1
2         -        example 2


Table Booking:

id        -        items_id        -         date                 -          status
----------------------------------------------------------------------------------
1         -        1                     -     2012-04-10         -          2
2         -        1                     -     2012-04-11         -          2
3         -        1                     -     2012-04-12         -          2
4         -        1                     -     2012-04-15         -          2
5         -        1                     -     2012-04-19         -          2
6         -        2                     -     2012-04-10         -          2
7         -        2                     -     2012-04-11         -          2
8         -        2                     -     2012-04-12         -          2
9         -        2                     -     2012-04-13         -          2
10       -        2                     -     2012-04-14         -          2


And I've two dates:

From :  2012-04-10
To:        2012-01-14


I need a query that retrieveme the "id" and "name" that has records in all the date range, for example in this case is the id "2"

Thanks!
nicojmbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Use a WHERE clause like this... WHERE `date` BETWEEN '2012-01-14' AND 2012-04-10'

but check your dates in the example.  Is 2012-01-14 what you really want?
Ray PaseurCommented:
The whole query might go something like this...
SELECT Items.id, Items.name, Booking.items_id, `date`
FROM Items, Booking
WHERE Items.id = Booking.items_id
AND `date` BETWEEN '2012-04-10' AND '2012-04-14'
nicojmbAuthor Commented:
Opps, no the dates are:

From :  2012-04-10
To:        2012-04-14

The  BETWEEN clause can not user because if a record in this range is missing in the table, the result retrieve the other records.

I need that the query check that all days exist in table, in the example the items_id "2" it's ok, but the items "1" no.

Regards!
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Ray PaseurCommented:
I don't get this part... The  BETWEEN clause can not user because if a record in this range is missing in the table, the result retrieve the other records.

If you want only the item_id for "2" this might be the right query.
SELECT Items.id, Items.name, Booking.items_id, `date`
FROM Items, Booking
WHERE Items.id = Booking.items_id
AND Items.id = 2
AND `date` BETWEEN '2012-04-10' AND '2012-04-14'

Open in new window

nicojmbAuthor Commented:
No,

I need all the items thats has records in the date range, but this records must exists in all date range.

For example the the items_id "1" not exist the day "2012-04-13" and "2012-04-14" and the BETWEEN cluase retrive me the id "1" but it's not correct, because not have all days in the range.

And the items_id "2" it's ok.

Now you understandme?

In need to check that has records with the same items_id in all days of the range...

Regards.
Ray PaseurCommented:
Let me see if I can paraphrase.  The query needs to find only those items that have rows for ALL of the dates in the range.  The problem is that each row is considered individually by the WHERE clause, but you need to consider the rows collectively.  Is that right?  If so, it might be easier to do this with some PHP as well as a query.
nicojmbAuthor Commented:
Yes it's right.

Either way I ok for me.!

Regards!
Ray PaseurCommented:
I do not have any test data so please allow me to use yours.  The lines prior to about #98 are just the setup to use the data base.  The strategy is to create an array of dates.  As we retrieve the rows from the results set we make a copy of the required dates for each new "bid" value, and we remove each date from our copy of array of dates.  If we exhaust the dates, we have a value that appears in all the require dates, so we save that one in the "good" array and continue until the results set is completed.

See http://www.laprbass.com/RAY_temp_nicojmb.php
<?php // RAY_temp_nicojmb.php
error_reporting(E_ALL);

// REQUIRED PHP 5.1+
date_default_timezone_set('America/Chicago');

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// LIVE DATABASE CREDENTIALS
require_once('RAY_live_data.php');

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES

// CREATING A TABLE
$sql
=
" CREATE TEMPORARY TABLE Items
( id   INT         NOT NULL AUTO_INCREMENT
, name VARCHAR(24) NOT NULL DEFAULT ''
, PRIMARY KEY(id)
)
"
;
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// LOAD THIS TABLE
MySQL_Query( "INSERT INTO Items (name) VALUES ('example 1')" );
MySQL_Query( "INSERT INTO Items (name) VALUES ('example 2')" );

// CREATING ANOTHER TABLE
$sql
=
" CREATE TEMPORARY TABLE Booking
( id       INT  NOT NULL AUTO_INCREMENT
, items_id INT  NOT NULL DEFAULT 0
, my_date  DATE NOT NULL DEFAULT '0000-00-00'
, status   INT  NOT NULL DEFAULT 2
, PRIMARY KEY(id)
)
"
;
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// LOAD THIS TABLE
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (1, '2012-04-10')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (1, '2012-04-11')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (1, '2012-04-12')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (1, '2012-04-15')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (1, '2012-04-19')" );

MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (2, '2012-04-10')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (2, '2012-04-11')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (2, '2012-04-12')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (2, '2012-04-13')" );
MySQL_Query( "INSERT INTO Booking (items_id, my_date) VALUES (2, '2012-04-14')" );


// TWO DATES
$fm = '2012-04-10';
$to = '2012-04-14';

// CREATE A RANGE OF DATES WITH KEY AND VALUE EQUAL
$d = array();
$a = $fm;
$z = $to;
while ($a <= $z)
{
    $x = date('Y-m-d', strtotime($a));
    $d[$x] = $x;
    $a     = date('Y-m-d', strtotime($a . ' +1 DAY'));
}


// A QUERY TO SELECT THINGS BY DATE
$sql
=
"
SELECT Items.id, Items.name, Booking.items_id AS bid, my_date
FROM Items, Booking
WHERE Items.id = Booking.items_id
AND my_date BETWEEN '$fm' AND '$to'
ORDER BY bid, my_date
"
;
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// ITERATE OVER THE RESULTS SET TO SEE WHAT WE ARE WORKING WITH
while ($row = mysql_fetch_assoc($res))
{
    print_r($row);
    echo "<br/>" . PHP_EOL;
}

// RESET THE RESULTS
if (!mysql_num_rows($res)) die('NOTHING');
mysql_data_seek($res, 0);

// SINCE WE ORDERED BY bid WE WILL USE THE ITERATOR TO SEE WHICH DATES ARE COVERED
$good = array();
$old  = '?';
while ($row = mysql_fetch_assoc($res))
{
    // INITIAILIZE FOR THIS bid
    if ($row['bid'] != $old)
    {
        // GET A FRESH COPY OF THE ARRAY OF DATES
        $now = $d;
        $old = $row['bid'];
    }

    // REMOVE THIS DATE FROM THE ARRAY OF DATES
    unset($now[$row['my_date']]);

    // IF ALL DATES ARE COVERED THE ARRAY WILL GO TO EMPTY
    if (empty($now)) $good[] = $row['name'];
}

// SHOW WHICH DATA ELEMENTS PASSED THE TEST
echo "<pre>";
var_dump($good);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nicojmbAuthor Commented:
That's works great.

Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.