SQL- Look for visitor that not visit second place after visit first place

Question

i'm trying to build query that can find out who not visit a stage after he visit the first stage.

Clients Table:

enter image description here

Plays table:

enter image description here

Tickets table:

+-----+----------------------+-------------------+------+------+
| CID | PLAY                 | SHOWTIME          | SEAT | COST |
+-----+----------------------+-------------------+------+------+
| 1   | Cats                 | 03/11/21 12:00:00 | 22   | 350  |
+-----+----------------------+-------------------+------+------+
| 2   | Phantom of the Opera | 02/11/21 15:00:00 | 44   | 150  |
+-----+----------------------+-------------------+------+------+
| 3   | Hamilton             | 02/11/21 15:00:00 | 123  | 450  |
+-----+----------------------+-------------------+------+------+
| 4   | Phantom of the Opera | 02/11/21 15:00:00 | 34   | 250  |
+-----+----------------------+-------------------+------+------+
| 3   | Hamilton             | 02/11/21 15:00:00 | 5    | 600  |
+-----+----------------------+-------------------+------+------+
| 2   | Phantom of the Opera | 02/11/21 15:00:00 | 6    | 70   |
+-----+----------------------+-------------------+------+------+
| 1   | Tina                 | 02/11/21 17:00:00 | 33   | 450  |
+-----+----------------------+-------------------+------+------+

so i have tired by first filter all clients that visit in A Stage. but i could not findout how to check if who also not visit in C Stage after he was in A Stage by date. any insight here?

SELECT cid,name
FROM(
SELECT tickets.cid,plays.play,tickets.showtime,clients.name,plays.stage
from TICKETS
INNER JOIN plays ON plays.play=tickets.play AND plays.showtime=tickets.showtime AND plays.stage='A'
INNER JOIN clients ON tickets.cid=clients.cid)

desire result: as you can see client with cid =1, not in the result becuse he visit in A stage and then he Visit in C stage

╔═════╦══════╗
║ cid ║ name ║
╠═════╬══════╣
║ 2   ║ Lior ║
╠═════╬══════╣
║ 3   ║ Josh ║
╠═════╬══════╣
║ 4   ║ Haim ║
╚═════╩══════╝

Provide ACCURATE source data. Now nothing allows to distinguish does rows 2,4,6 in Tickets table matches stage A or B. — Akina, Dec 01 '20 at 10:52

GMB · Answer 1 · 2020-12-01T11:09:28.113

1

I think you can use aggregation and having:

select c.cid, c.name
from tickets t
inner join plays p on p.play = t.play and p.showtime = t.showtime
inner join clients c on t.cid = c.cid
where p.stage in ('A', 'B')
group by c.cid, c.name
having 
    min(case when p.stage = 'A' then p.showtimie end) > max(case when p.stage = 'B' then p.showtimie end)
    or max(case when p.stage = 'B' then p.showtimie end) is null

Basically this brings all plays of each client, and then compares the date of the earlierst "A" stage to that of the latest "B" stage, and ensures that the former is greater than the latest: that is, there is no "B" after the first "A" (or there is no "B" at all).

edited Dec 01 '20 at 11:09

answered Dec 01 '20 at 10:53

GMB

216,147
25
84
135

thanks, but i'm getting: ORA-00904: "C"."CID": invalid identifier – chuks Dec 01 '20 at 10:56
@chuks: there was a missing alias declaration. Fixed. – GMB Dec 01 '20 at 10:57
1

@chuks: this is an Oracle error message, so why did you tag your question MySQL? – GMB Dec 01 '20 at 10:57
thanks but still: ORA-00904: "P"."SHOWTIMIE": invalid identifier – chuks Dec 01 '20 at 10:58
ok i solved it. but new error: not a GROUP BY expression – chuks Dec 01 '20 at 11:03
@chuks: that is because I provided a MySQL query... Fixed for Oracle just now. – GMB Dec 01 '20 at 11:09

Slava Rozhnev · Answer 2 · 2020-12-01T11:03:52.520

0

I mean the next query can help you:

SELECT DISTINCT clients.cid, clients.name
FROM tickets
INNER JOIN clients ON tickets.cid = clients.cid
INNER JOIN plays playA ON playA.play = tickets.play 
          AND playA.showtime = tickets.showtime 
          AND playA.stage = 'A'
LEFT JOIN plays playC ON playC.play = tickets.play 
          AND playC.showtime = tickets.showtime 
          AND playC.stage = 'C'
          AND playC.showtime > playA.showtime
WHERE
    playC.play IS NULL;

edited Dec 01 '20 at 11:03

answered Dec 01 '20 at 10:58

Slava Rozhnev

9,510
6
23
39

how does is filtering if client goes to a A Stage in date : 2/11/21. and then go to C stage in 3/11/21. i dont see date filtering here. can you explain to me the solution ? – chuks Dec 01 '20 at 11:00
still not working, it showing me also clintes that visit in A Stage and then visited in C Stage. can you check again please ? – chuks Dec 01 '20 at 11:13

score 0 · Answer 3 · answered Dec 01 '20 at 11:07

You can use the COUNT analytical function as follows:

SELECT DISTINCT CID, NAME FROM
(SELECT T.CID,
       C.NAME,
       COUNT(DISTINCT T.PLAY) OVER (PARTITION BY T.CID) AS CNT
  FROM TICKETS T
 INNER JOIN CLIENTS C
ON T.CID = C.CID)
WHERE CNT = 1

Or you can use the GROUP BY and HAVING as follows:

SELECT T.CID,
       C.NAME
  FROM TICKETS   T
 INNER JOIN CLIENTS   C
ON T.CID = C.CID
 GROUP BY T.CID,
          C.NAME
HAVING COUNT(1) = 1

SQL- Look for visitor that not visit second place after visit first place

3 Answers3