Mysql complement with many tables and grouping

Question

I hope the answer is simple and I'm silly.

So I've two tables: day (with PK id_day) and hour (with PK id_hour).

I've an another table tilt_time which has id_day, id_hour fields and a plus FK field (which has prohibited times). E.g. tilt_time has the following rows (id_day, id_hour, id_target):

1,1,1
1,2,1
1,3,1
1,1,2
1,2,2

In table day id_day is between 1 and 5 (monday to friday) and hour id_hour is between 1 and 6 (so you can imagine a day_hour table with 5*6=30 values).

So I want query id_target=1's free periods and id_target=2's free periods (everbody's free periods). So I want similar:

1,4,1
1,5,1
1,6,1
2,*,1
3,*,1
4,*,1
5,*,1
// id_target=2
1,3,2
1,4,2
1,5,2
1,6,2
2,*,2
3,*,2
4,*,2
5,*,2

(* means all valid id_hour values, so every line with * represents 6 different lines)

How can I do this query?

ypercubeᵀᴹ · Accepted Answer · 2012-07-29T13:32:00.763

0

If you have a target table with all id_target, the answer is simple:

SELECT 
    d.id_day, h.id_hour, t.id_target
FROM 
    day AS d
  CROSS JOIN
    hour AS h
  CROSS JOIN
    target AS t
WHERE
    (d.id_day, h.id_hour, t.id_target) NOT IN
      ( SELECT id_day, id_hour, id_target
        FROM tilt_time
      )
;

or the (probably more efficient):

SELECT 
    d.id_day, h.id_hour, t.id_target
FROM 
    day AS d
  CROSS JOIN
    hour AS h
  CROSS JOIN
    target AS t
  LEFT JOIN
    tilt_time AS tt
      ON  tt.id         = d.id_day
      AND tt.id_hour    = h.id_hour
      AND t.t.id_target = t.id_target
WHERE 
    tt.id_target iS NULL ;

If you don't have a target table, replace the target AS t in the code above with:

( SELECT DISTINCT id_target FROM tilt_time ) AS t

edited Jul 29 '12 at 13:32

answered Jul 29 '12 at 13:14

ypercubeᵀᴹ

113,259
19
174
235

Wow :) It's too simple :) I need some modifications but almost perfect! Thanks! – uzsolt Jul 29 '12 at 13:29
"If you don't have a target table, replace the target AS t in the code above with" - I did this modification before your edit because I need only those targets which are in `tilt_time`. – uzsolt Jul 29 '12 at 13:40

Mehran · Answer 2 · 2012-07-29T13:33:27.443

0

The following query returns id_day and id_hour in which both target ids of 1 and 2 have no record in tilt_time.

SELECT tg1_free.id_day, tg2_free.id_hour
FROM (
    SELECT
        day.id_day, hour.id_hour
    FROM day, hour
    WHERE
        (day.id_day, hour.id_hour) NOT IN (SELECT id_day, id_hour FROM tilt_time WHERE id_target = 1)
) tg1_free
INNER JOIN (
    SELECT
        day.id_day, hour.id_hour
    FROM day, hour
    WHERE
        (day.id_day, hour.id_hour) NOT IN (SELECT id_day, id_hour FROM tilt_time WHERE id_target = 2)
) tg2_free ON (tg1_free.id_day = tg2_free.id_day AND tg1_free.id_hour = tg2_free.id_hour)

[EDIT]

For all of target ids there is (it's even easier):

SELECT id_day, id_hour
FROM day, hour
WHERE
    (day.id_day, hour.id_hour) NOT IN (SELECT id_day, id_hour FROM tilt_time);

edited Jul 29 '12 at 13:33

answered Jul 29 '12 at 13:17

Mehran

15,593
27
122
221

Sorry, but the two `id_target` isn't enough. I need all `id_target` (I've wrote: "everbody's free periods"). Can you modify your answer? Thanks! – uzsolt Jul 29 '12 at 13:25
Sorry again :) My english is poor. I want all free time PER `id_target` not free times without `id_target`. – uzsolt Jul 29 '12 at 13:46
Then you just need to include your id_target in subquery's where clause – Mehran Jul 30 '12 at 12:16

Mysql complement with many tables and grouping

2 Answers2