So im banging my head against a wall here trying to get this to work correctly.
I have two tables, Callsigns:
[upl-image-preview url=https://board.phpbuilder.com/assets/files/2019-07-13/1563038625-870643-callsigns.png]
and Slots:
[upl-image-preview url=https://board.phpbuilder.com/assets/files/2019-07-13/1563038625-886406-slots.png]
What i'm trying to do is get the row data for each DISTINCT Callsign
where the LEFT(Received
,2) = Current day of the month and is MAX for the given callsign, i then need to get the associated flight info from the Callsigns Table for each distinct callsign returned.
The format of Received
is ddhhmm.
So for Example if the Slots table has the following rows:
1 | UAE36 | 131433 | 1613 | CE 81 | 2019-07-06 17:29:48
2 | UAE36 | 131533 | 1603 | CE 81 | 2019-07-06 17:29:48
3 | UAE36 | 131535 | 1600 | CE 81 | 2019-07-06 17:29:48
4 | UAE36 | 131555 | 1601 | CE 81 | 2019-07-06 17:29:48
5 | UAE50 | 131433 | 1613 | CE 81 | 2019-07-06 17:29:48
6 | UAE50 | 131533 | 1603 | CE 81 | 2019-07-06 17:29:48
7 | UAE50 | 131535 | 1600 | CE 81 | 2019-07-06 17:29:48
8 | UAE50 | 131555 | 1601 | CE 81 | 2019-07-06 17:29:48
It would return:
Callsign | Airline | FltNo | Dest | CTOT | Recieved
UAE36 | EK | 36 | DXB | 1601 | 131555
UAE50 | EK | 50 | LHR | 1601 | 131555
my knowledge and understanding of Joins is very limited and have been battling this problem for about 2 weeks now, every time i think i have it it proves otherwise.
My Current Query:
SELECT * FROM Slots INNER JOIN( SELECT Callsign, max(RIGHT(Slots.Received,4)) ls, CTOT FROM Slots WHERE LEFT(Slots.Received,2) = ? GROUP BY Callsign) B ON Slots.Callsign = B.Callsign and right(Slots.Received,4) = B.ls ORDER BY `Slots`.`Callsign` ASC
This query however randomly pulls slot records where the start of Received
doesn't match the current day?