All geek questions in one place

Correlated Subquery in MySQL CASE Statement

Here is a brief explanation of what I'm trying to accomplish; Below is my request.

There are 4 tables and 1 view that are relevant to this particular query (sorry that the names look messy, but they follow a strict convention that would make sense if you saw the full list):

Performance can have many Performer s, and these associations are stored in PPerformer . Fan can have favorites stored in Favorite_Performer . The _UpcomingPerformances contains all the information needed to display a user-friendly list of upcoming activities.

My goal is to select all the data from _UpcomingPerformances, and then add another column that indicates whether this Performance Performer that Fan has added as his favorite. This includes selecting a list of artists related to performance, as well as a list of artists who are in Favorite_Performer for this fan, and the intersection of two arrays to determine something in common.

When I execute the query below, I get error message #1054 - Unknown column 'up.pID' in 'where clause' . I suspect this has something to do with the misuse of Correlated subqueries , but as far as I can tell what I'm doing should work. It works when I replace up.pID (in the WHERE t2 clause) with a hard-coded number, and yes, pID is an existing _UpcomingPerformances column.

Thanks for any help you can provide.

 SELECT up.*, CASE WHEN EXISTS ( SELECT * FROM ( SELECT RID FROM Favorite_Performer WHERE FanID = 107 ) t1 INNER JOIN ( SELECT r.ID as RID FROM PPerformer pr JOIN Performer r ON r.ID = pr.Performer_ID WHERE pr.Performance_ID = up.pID ) t2 ON t1.RID = t2.RID ) THEN "yes" ELSE "no" END as pText FROM _UpcomingPerformances up
+4
source share
1 answer

The problem is with the area. Nested Selects make the up table invisible in internal selection. Try the following:

 SELECT up.*, CASE WHEN EXISTS ( SELECT * FROM Favorite_Performer fp JOIN Performer r ON fp.RID = r.ID JOIN PPerformer pr ON r.ID = pr.Performer_ID WHERE fp.FanID = 107 AND pr.Performance_ID = up.pID ) THEN 'yes' ELSE 'no' END as pText FROM _UpcomingPerformances up
+3
source

Source: https://habr.com/ru/post/1499888/

More articles:


All Articles