3

Complicated question: I have 3 tables that I am joining to pull data. The first is a 'shift' table; the second a 'schedule' table, and the last is a 'shift_info' table.

shift_info

+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| date                 | date         | NO   | MUL | NULL    |       |
| name                 | varchar(20)  | YES  |     | NULL    |       |
| initial              | varchar(1)   | YES  |     | NULL    |       |
| start_time           | time         | YES  |     | NULL    |       |
| end_time             | time         | YES  |     | NULL    |       |
+----------------------+--------------+------+-----+---------+-------+

shift

+--------------------+----------+------+-----+---------+----------------+
| Field              | Type     | Null | Key | Default | Extra          |
+--------------------+----------+------+-----+---------+----------------+
| id                 | int(8)   | NO   | PRI | NULL    | auto_increment |
| schedule_id        | int(6)   | NO   | MUL | NULL    |                |
| user_id            | int(3)   | NO   |     | NULL    |                |
| created_on         | datetime | NO   |     | NULL    |                |
| replaced_id        | int(8)   | YES  |     | NULL    |                |
| created_by_user_id | int(3)   | NO   |     | NULL    |                |
+--------------------+----------+------+-----+---------+----------------+

schedule

+--------------+---------+------+-----+------------+----------------+
| Field        | Type    | Null | Key | Default    | Extra          |
+--------------+---------+------+-----+------------+----------------+
| id           | int(7)  | NO   | PRI | NULL       | auto_increment |
| date         | date    | NO   | PRI | 0000-00-00 |                |
| shift        | char(1) | NO   | PRI |            |                |
| shift_id     | int(3)  | YES  |     | NULL       |                |
+--------------+---------+------+-----+------------+----------------+

The schedule table stores a list of shifts, identifed by an initial. The shift table stores the details of shift. The shift info table lists the shifts available at a certain date.

SELECT * FROM shift as a 
LEFT JOIN shift as b ON a.replaced_id = b.id 
LEFT JOIN schedule ON a.schedule_id = schedule.id 
left JOIN shift_info AS e ON schedule.shift = e.initial 
WHERE a.user_id = 58 
    AND schedule.date >= 20151205 
    AND e.date = (SELECT DISTINCT date 
                  FROM shift_info 
                  WHERE date <= 20151205 ORDER BY date DESC LIMIT 1);

My question is: is there a way to make the subquery search in 'shift_info' reflect the data in the joined table 'schedule'? Currently if the date in shift_info is greater than the date in schedule, the database looks up the wrong shift_info.

I would like to know if you can search one date from the shift_info table in a different way to how I am trying to limit it.

1

Dont know why you have a subselect at all, that query can be reduced to this:

SELECT * FROM shift AS a 
LEFT JOIN shift AS b ON a.replaced_id = b.id 
LEFT JOIN schedule AS s ON a.schedule_id = s.id AND s.date >= '2015-12-05' 
left JOIN shift_info AS si ON s.shift = si.initial AND si.date <= '2015-12-05'
WHERE a.user_id = 58;

Generally you want join conditions applied in the ON clause, particularly when they affect the actual join, as is the case with LEFT JOIN. I think this fulfills what you are asking as well (since the schedule and shift_info start at the same start time).

  • The subquery is because the info on the shift_info table changes. The original query will look up all shifts from 2015-12-05 forward. The infomation about the shift is the closest to the date on the table in the past (one day only). The problem is the shift_info can change in the future (2016-01-01 for instance), but I only want this to match dates on the schedule > 2016-01-01. I only want the info as it applies to the date on the schedule table. – smilingfrog Jan 11 '16 at 22:59
1

Thanks to @CSTobey and @jkavalik for pointing me in the right direction. This is the sql that works for me:

SELECT * FROM shift AS a 
LEFT JOIN shift  b ON a.replaced_id = b.id 
INNER JOIN schedule ON a.schedule_id = schedule.id AND schedule.date >= 20151205 
INNER JOIN shift_info AS e ON schedule.shift = e.initial 
AND e.date = (SELECT shift_info.date  FROM shift_info WHERE date < schedule.date    ORDER BY date DESC LIMIT 1) 
WHERE a.user_id = 58 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.