MySQL order by before group by plus join afterwards

Question

after hours of trying I thought I ask for your help here. My problem is the following:

I have two tables. One table contains general event data and the second table contains single events that refer to the general table (there can be several single events refering to one general event row).

The structure is as following:

1. General events table "events":

id | club_id | date_created | description | imageLink | ....

2. Single events table: "events_single"

id | events_id (id from events table) | valid_from | valid_until

All timestamps are unix timestamps

So what I want to get is the nearest single event in the future for a club with a certain club_id. The single event row shall be joined with the corresponding event row.

Form the following link I managed to get the nearest single events for all clubs : MySQL order by before group by

The query looks like this:

SELECT p1 . *
FROM events_single p1
INNER JOIN (
    SELECT min( valid_from ) AS firstEvent, events_id
    FROM events_single
    WHERE events_single.valid_from >= UNIX_TIMESTAMP()
    GROUP BY events_id
)p3 ON p1.events_id = p3.events_id
    AND p1.valid_from = p3.firstEvent
    ORDER BY p1.valid_from ASC

This returns for example:

id  |events_id  |valid_from|valid_until
4   |1          |1446706800|1446793200
39  |7          |1446966000|1447052400
14  |4          |1447311600|1447398000

The problem is that I now need to join this table again with the events table to get only the single events for a certain club.

E.g. if the general events table is

id  | club_id   | date_created  | ....
1   | 1         | 1446793200    | ....   <------            
2   | 2         | 1456793235    | ....              
3   | 5         | 1458735234    | ....
4   | 1         | 1458783425    | ....   <------    
5   | 2         | 1458953256    | ....
6   | 4         | 1461983283    | ....
7   | 5         | 1461993452    | ....

and I want to get now all single events for club_id = 1 and should return both tables joined:

id  |events_id  |valid_from|valid_until|club_id     | date_created  | ....
4   |1          |1446706800|1446793200 |1           | 1446793200    | ....  
14  |4          |1447311600|1447398000 |1           | 1458783425    | ....

I tried the following query:

SELECT p1.*, p2.*
FROM events_single p1, events p2
INNER JOIN (
    SELECT min( valid_from ) AS firstEvent, events_id
    FROM events_single
    WHERE events_single.valid_from >= UNIX_TIMESTAMP()
    GROUP BY events_id
)p3 ON p1.events_id = p3.events_id
    AND p1.valid_from = p3.firstEvent
    WHERE p2.club_id = 1
    ORDER BY p1.valid_from ASC

but it I get an error:

#1054 - Unknown column 'p1.events_id' in 'on clause' 

If I replace 'p1.events_id' with 'events_single.events_id' I get the same error.

Why is that? Or the better question: What's the correct query?

Thanks a lot in advance!

Answer 1

It seems you need to again join with Events Table again. In some other ways alias p1 is not visible in the ON Clause , as you again added events p2. Please try this query as below which will fix your issue.

SELECT p1.*, p2.*
FROM events_single p1
INNER JOIN (
    SELECT min( valid_from ) AS firstEvent, events_id
    FROM events_single
    WHERE events_single.valid_from >= UNIX_TIMESTAMP()
    GROUP BY events_id
)p3 ON (p1.events_id = p3.events_id
    AND p1.valid_from = p3.firstEvent)
INNER JOIN 
events p2 ON (p2.id = p3.event_id)
    WHERE p2.club_id = 1
    ORDER BY p1.valid_from ASC

We can optimize the above query as follows

SELECT events.*, es_tmp.*
    FROM events
    INNER JOIN (
        SELECT min( valid_from ) AS firstEvent, events_id
        FROM events_single
        WHERE events_single.valid_from >= UNIX_TIMESTAMP()
        GROUP BY events_id
    )es_tmp ON (events.id = es_tmp.events_id)
    WHERE events.club_id = 1
        ORDER BY es_tmp.firstEvent ASC