MySQL - where clause on triple nested select

Question

I'm writing the most complicated query I've personally ever done... and I'm stuck!

Here's the code:

SELECT 
    el.UID, 
    (
        SELECT 
            SUM(booked)  
        FROM 
            (
                SELECT 
                ts.*, max(ts.dateStored) 
                FROM eventsDetails ed 
                JOIN ticketSales ts ON ts.DUID = ed.UID 
                WHERE ed.event_masterEvent = 1147 
                GROUP BY ts.DUID
            ) a 
    ) as booked,
    el.event_confirmed, 
    el.event_title, 
    (
        SELECT 
            GROUP_CONCAT(g.colour) 
        FROM eventsGenre eg 
        JOIN genres g ON g.UID = eg.GUID 
        WHERE eg.EUID = el.UID
    ) AS genreCodes, 
    (
        SELECT 
            GROUP_CONCAT(g.genre) 
        FROM eventsGenre eg 
        JOIN genres g ON g.UID = eg.GUID 
        WHERE eg.EUID = el.UID
    ) AS genreNames 
FROM eventsList el 
JOIN eventsDetails ed ON el.UID = ed.event_masterEvent 
WHERE el.event_active='1' 
AND ed.event_eventDateAndTime >= CURDATE() 
GROUP BY el.UID 
ORDER BY ed.event_eventDateAndTime ASC

On the line "WHERE ed.event_masterEvent = 1147", I wan't to change that to "WHERE ed.event_masterEvent = el.UID". 1147 was just to test that the code works... but now I need to reference the overall event ID.

Whenever I do that, I get an error saying the column doesn't exist.

I don't understand, because further down the query, I am able to say "WHERE eg.EUID = el.UID" within a sub select.

I don't know what I'm doing wrong... as I said before, I'm very much out of my comfort zone at the moment and looking for some MySQL gurus to ride in and explain where I've gone wrong.

I've got a feeling I need to add another join somewhere, but I can't work out where. I tried to search for the answer, but I'm not really sure what search terms to use - so I'm not getting very far.

Any help would be greatly appreciated!

Here's a fiddle of the issue: http://sqlfiddle.com/#!9/c77a98/2

I don't understand why you need that nested `SELECT`. What's the point of the unused `MAX(ts.dateStored)`? — Barmar, Mar 14 '18 at 22:40
If you're trying to get the `booked` value on the last `ts.dateStored` for each `DUID`, that's not the correct way. See [row with max value of a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1) — Barmar, Mar 14 '18 at 22:43
As I said - I'm new to this... so thank you for the advice. I will go and take a look. However, is there a reason that I can't reference el.UID from the nested select - or is there a way to do that? — Blind Trevor, Mar 14 '18 at 22:46
From the [manual](https://dev.mysql.com/doc/refman/5.7/en/derived-tables.html): Derived tables (subqueries in a FROM clause of a SELECT) can't contain outer references or references to other tables of the same SELECT — Nick, Mar 14 '18 at 22:49
Is there a 1:1 correspondence between ed.UID and ed.event_masterEvent? — Nick, Mar 14 '18 at 23:08
no... there is a 1:1 correspondence between el.UID and ed.event_masterEvent - is that what you meant? — Blind Trevor, Mar 14 '18 at 23:19
No, was hoping there was as it would make it possible to simplify the derived query. I think you are going to need a JOIN. — Nick, Mar 14 '18 at 23:25

fubar · Accepted Answer · 2018-03-15T01:11:50.533

1

The query you posted is grossly overcomplicated, and can be made significantly more readable if you use some JOINs.

I've managed to rewrite (untested) most of your query, as follows, but I am unsure about refactoring the booked portion, because it's not clear what you're trying to achieve without having a solid schema and sample data to reference.

If you'd care to create a working SQL fiddle with your schema and data, I'll have a stab at finishing it. If not, hopefully the following might help you to simplify and solve your problem.

SELECT 
    el.UID,
    el.event_confirmed, 
    el.event_title, 
    GROUP_CONCAT(DISTINCT g.genre SEPARATOR ', ') AS genreNames,
    GROUP_CONCAT(DISTINCT g.colour SEPARATOR ', ') AS genreCodes,
    SUM(ts.booked) AS booked
FROM eventsList el
INNER JOIN eventsDetails ed ON (el.UID = ed.event_masterEvent AND ed.event_eventDateAndTime >= CURDATE())
LEFT JOIN eventsGenre eg ON (el.UID = eg.EUID)
LEFT JOIN genres g ON (eg.GUID = g.UID)
LEFT JOIN (
    SELECT ts.*, MAX(ts.dateStored)
    FROM eventsDetails ed 
    INNER JOIN ticketSales ts ON ts.DUID = ed.UID 
    GROUP BY ts.DUID
) AS ts ON (ed.UID = ts.DUID)
WHERE el.event_active = 1
AND el.UID = 1147
GROUP BY el.UID
ORDER BY ed.event_eventDateAndTime ASC;

edited Mar 15 '18 at 01:11

answered Mar 14 '18 at 22:46

fubar

16,918
4
37
43

You should probably use `DISTINCT` in the `GROUP_CONCAT`s, otherwise you'll get duplication because of the additional join. But this answer doesn't address the real problem in his query, which is how to get the `booked` values that he wants. – Barmar Mar 14 '18 at 22:51
Good call on the `DISTINCT`. I realise it doesn't answer his question right now, but the query he posted is a mess, so I've tried to simplify it, and with some more information from OP I can answer his question too. But as I said in my answer, he can take it or leave it. – fubar Mar 14 '18 at 22:54
I've never created a fiddle before... I'm just checking it out now. I don't doubt that the query is over complicated... but, I'm trying to get better :) – Blind Trevor Mar 14 '18 at 22:54
I think you need to join with a subquery that gets `SUM(booked) ... GROUP BY event_masteruid, DUID`, and join on `el.UID = DUID`. – Barmar Mar 14 '18 at 22:57
As the `MAX(ts.dateStored)` isn't used, I was thinking to just `LEFT JOIN ticketSales ts ON (ed.UID = ts.DUID)`, and to `SUM(ts.booked) AS booked`, as the `GROUP BY el.UID` should handle the aggregation. – fubar Mar 14 '18 at 23:09
@BlindTrevor - I have updated my query. It now gives the same result as the query in your SQL fiddle, but without the need for `WHERE ed.event_masterEvent = 1147` on the sub query. Is this what you needed? – fubar Mar 15 '18 at 01:13
@fubar - you're a flipping genius! Thank you so much. I will pick this apart and learn from it... but it's working perfectly! – Blind Trevor Mar 15 '18 at 02:33

MySQL - where clause on triple nested select

1 Answers1