CODEWITHSUNDEEP
mysqlsqljoin
Wes
Wes

Reputation: 764

MySQL Query - Join Three Tables

I have three MySQL tables that look like the following:

inventory table

  sn    |    hostname
-----------------------
  1234       host123
  2345       host456
  4567       host789
  6789       host910

reservation table

  orderid   |    serial    |   start_date    |    end_date    
----------------------------------------------------------
      1           1234         2015-09-10        2015-10-25
      2           2345         2015-10-21        2016-02-01
      2           4567         2015-10-21        2016-02-01
      3           6789         2015-08-01        2015-12-31

order detail table

  order_id    |    assigned_to   
---------------------------------
      1              Mark
      2              John
      3              Paul

I'm attempting to do a query from all 3 tables like this:

SELECT `serial`, `hostname`, `orderid`, `start_date`, `end_date`, `assigned_to` 
FROM `reservation`, `inventory`, `orders` 
WHERE `sn` IN(SELECT `serial` 
              FROM `reservation` 
              WHERE '2015-12-10' <= `end_date` AND '2015-12-10' >= `start_date`) 
      AND `serial` = `sn` AND `orderid` = `order_id`

In my query example, I just want to display results that fit within the date range I am choosing, but I end up getting all the results regardless of the date range I choose. Example query result is this:

  serial   |   hostname  |   orderid   |   start_date   |   end_date   |   assigned_to 
-----------------------------------------------------------------------------------------
   1234        host123          1          2015-09-10      2015-10-25       Mark
   2345        host456          2          2015-10-21      2016-02-01       John
   4567        host789          2          2015-10-21      2016-02-01       John
   6789        hout910          3          2015-08-01      2015-12-31       Paul

But the result I desire, based on the above example query, is this:

  serial   |   hostname  |   orderid   |   start_date   |   end_date   |   assigned_to 
-----------------------------------------------------------------------------------------
   2345        host456          2          2015-10-21      2016-02-01       John
   4567        host789          2          2015-10-21      2016-02-01       John
   6789        hout910          3          2015-08-01      2015-12-31       Paul

What am I doing wrong with my query, and how can I achieve the desired result? I've tried the same with INNER JOINS as well but end up with the same result.

Upvotes: 1

Views: 1038

Answers (1)

Dave
Dave

Reputation: 3658

I would get rid of the sub-query since it isn't necessary. I'd also do proper JOIN while I'm at it.

SELECT 
    `serial`, `hostname`, `orderid`, `start_date`, `end_date`, `assigned_to`
FROM 
    `inventory`
JOIN
    `reservation` ON `serial` = `sn`
JOIN
    `orders` ON `order_id` = `orderid`
WHERE 
    `start_date` <= '2016-12-10' AND `start_date` >= '2015-12-10'

Upvotes: 1

Related Questions