0

Context: event scheduling software.

Thanks to answers on this question about finding duplicate values, I have come up with the following query, which works perfectly for finding users who are scheduled for two events at the same time:

SELECT  *
FROM    panel_participants pp
WHERE   EXISTS
    (
    SELECT  1
    FROM    panel_participants pps
    WHERE   pp.user_id = pps.user_id
    AND     pp.day_time_slot_id = pps.day_time_slot_id
    AND     pp.day_time_slot_id > 0
    LIMIT 1, 1
    )

Now what I need is a very similar query, with one difference: I want to find items where pp.day_time_slot_id = pps.day_time_slot_id + 2 (which will show me where a user has been scheduled for two consecutive events). However, this returns zero rows:

SELECT  *
FROM    panel_participants pp
WHERE   EXISTS
    (
    SELECT  1
    FROM    panel_participants pps
    WHERE   pp.user_id = pps.user_id
    AND     pp.day_time_slot_id = pps.day_time_slot_id + 2
    AND     pp.day_time_slot_id > 0
    LIMIT 1, 1
    )

And I know there are rows that should qualify, e.g. user 503 is scheduled for items in time slots 23 and 25.

I've tried setting variables based on the replies to this question, but I think I'm not understanding how variables work and are passed into and out of queries, because everything I try returns either an error or zero rows. I've confirmed that day_time_slot_id is an integer in case that makes a difference.

Help?

Community
  • 1
  • 1
Rose Fox
  • 39
  • 1
  • 6
  • show us some sample data – Stephan Jun 20 '13 at 07:18
  • please provide some sample data and expected result – Fathah Rehman P Jun 20 '13 at 07:26
  • You first query doesn't appear to do what you want it to do. It is taking a record and checking if an identical record appears in the same table. As there is nothing to exclude the original record that will always match itself. Easy enough to fix assuming you have something like a panel id. – Kickstart Jun 20 '13 at 08:24
  • Oops, sorry! I was editing this while fighting a head cold and put in an old version of the query--should have been SELECT *, not SELECT pp.user_id. Fixed. – Rose Fox Jun 20 '13 at 19:02

1 Answers1

1

Assuming you have a panel_id on the panel_participants table the following should get you a list of people who have 2 panels to participate in for the same time slot:-

SELECT  DISTINCT pp.user_id
FROM    panel_participants pp
INNER JOIN panel_participants pps
ON pp.user_id = pps.user_id
AND pp.day_time_slot_id = pps.day_time_slot_id
AND pp.panel_id != pps.panel_id
AND pp.day_time_slot_id > 0

The following should find those with appointments for 2 consecutive slots (although not sure why consecutive slots should be 2 apart).

SELECT  DISTINCT pp.user_id
FROM    panel_participants pp
INNER JOIN panel_participants pps
ON pp.user_id = pps.user_id
AND pp.day_time_slot_id + 2 = pps.day_time_slot_id
AND pp.day_time_slot_id > 0

Afraid I can't test this (or be certain) as you have posted no table layouts or data.

Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • Thank you so much! That does appear to give me a list of people who are scheduled for back-to-back items. (1 p.m. is 2 slots away from 2 p.m. because there's a 1:30 slot in between that I'm ignoring for these purposes.) – Rose Fox Jun 20 '13 at 19:05