MySQL: Multiple table join

Question

I got a system to request tours for drivers. If more than one driver requests the same tour it goes to the one who started to work with us first. If they started on the same day I need the birthday so that the oldest one gets the tour.

Table 1: Data of the employees SEN = Date of Entry, BIR = Birthday

Table 2: Tours

Table 3: Requests with the tourid as well as the employeeid

Here is my query:

 SELECT T.*, ER.SEN, requestcnt FROM tours T LEFT OUTER JOIN (  SELECT
 R.tourid, count(R.requestid) AS requestcnt,  MIN(E.SEN) AS SEN
         FROM requests R
         INNER JOIN employees E ON E.employeeid = R.employeeid WHERE Funktion = 'XY'
         GROUP by R.tourid 
         ) SR ON ER.tourid = U.tourid

How do I get to the birthday of the employee that matches the lowest seniority for that tour. If I just add the birthday-field to the seconds select statement I get the birthday of the employee with the overall lowest seniority even if he/she did not request the tour.

The goal is to have everything in one query, because of the amount of tours. This query is for the overview-page so I can show smilies depending on the availability of the tour.

Thanks for any advice!

Please edit your question with sample data, desired results, and explain why your query is not working. — Gordon Linoff, Apr 30 '14 at 11:46

Kickstart · Accepted Answer · 2014-06-04T08:27:58.017

Probably doing another join against the requests and employees.

Something like this:-

SELECT T.*, ER2.SEN, ER2.BIR, requestcnt 
FROM tours T 
LEFT OUTER JOIN 
(
    SELECT R.tourid, count(R.requestid) AS requestcnt,  MIN(E.SEN) AS SEN
    FROM requests R
    INNER JOIN employees E ON E.employeeid = R.employeeid 
    WHERE Funktion = 'XY'
    GROUP by R.tourid 
) ER 
ON ER.tourid = U.tourid
LEFT OUTER JOIN 
(
    SELECT R.tourid, E.SEN,  MIN(E.BIR) AS BIR
    FROM requests R
    INNER JOIN employees E ON E.employeeid = R.employeeid 
    WHERE Funktion = 'XY'
    GROUP by R.tourid, E.SEN
) ER2 
ON ER2.tourid = U.tourid
AND ER.SEN = ER2.SEN

EDIT - For the revised request

Getting the 3 of anything is awkward as you can't really use MIN / MAX functions to get it (with a lot of effort you possibly could by using them to find and exclude the min, then again with the results of that, etc). You can't really use limit easily with joined sub queries as it will work on the overall results of the query, not per group.

2 ways I can think of off the top of my head. Firstly adding a sequence number to a sub query which gets the results in seniority order, which resets the tourid changed.

Something like this (not tested):-

SELECT T.*, ER2.SEN, ER2.BIR, requestcnt 
FROM tours T 
LEFT OUTER JOIN 
(
    SELECT R.tourid, count(R.requestid) AS requestcnt
    FROM requests R
    INNER JOIN employees E ON E.employeeid = R.employeeid 
    WHERE Funktion = 'XY'
    GROUP by R.tourid 
) ER 
ON ER.tourid = T.tourid
LEFT OUTER JOIN 
(
    SELECT R.tourid, E.employeeid, E.SEN,  E.BIR, @seq:=IF(R.tourid=@tourid, @seq+1, 1) AS seq
    FROM requests R
    INNER JOIN employees E ON E.employeeid = R.employeeid 
    CROSS JOIN (SELECT @seq:=0, @tourid:=0)
    WHERE Funktion = 'XY'
    ORDER BY R.tourid, E.SEN, E.BIR, E.employeeid
) ER2 
ON ER2.tourid = T.tourid
AND ER2.seq = 3

Another way would be using correlated sub queries on the SELECT. This will probably slow down dramatically as the number of rows returned increases:-

SELECT T.*, 
        (
            SELECT E.SEN
            FROM requests R
            INNER JOIN employees E ON E.employeeid = R.employeeid 
            WHERE Funktion = 'XY'
            AND R.tourid = T.tourid
            ORDER BY E.SEN, E.BIR
            LIMIT 3,1
        ),
        (
            SELECT E.BIR
            FROM requests R
            INNER JOIN employees E ON E.employeeid = R.employeeid 
            WHERE Funktion = 'XY'
            AND R.tourid = T.tourid
            ORDER BY E.SEN, E.BIR
            LIMIT 3,1
        ),
        requestcnt 
FROM tours T 
LEFT OUTER JOIN 
(
    SELECT R.tourid, count(R.requestid) AS requestcnt
    FROM requests R
    INNER JOIN employees E ON E.employeeid = R.employeeid 
    WHERE Funktion = 'XY'
    GROUP by R.tourid 
) ER 
ON ER.tourid = T.tourid

Or possibly in the WHERE clause (again not tested)

SELECT T.*, EM.SEN, EM.BIR, EM.employeeid,
        requestcnt 
FROM tours T 
CROSS JOIN employees EM
LEFT OUTER JOIN 
(
    SELECT R.tourid, count(R.requestid) AS requestcnt
    FROM requests R
    INNER JOIN employees E ON E.employeeid = R.employeeid 
    WHERE Funktion = 'XY'
    GROUP by R.tourid 
) ER 
ON ER.tourid = T.tourid
WHERE EM.employeeid = 
(
    SELECT E.employeeid
    FROM requests R
    INNER JOIN employees E ON E.employeeid = R.employeeid 
    WHERE Funktion = 'XY'
    AND R.tourid = T.tourid
    ORDER BY E.SEN, E.BIR
    LIMIT 3,1
)

I have a problem now. With a similar request like the one you posted I need the employee with the third-highest seniority. The post above is for the oldest (seniority and age). I tried to modify with a LIMIT, but I don't get to the solution. Thanks for any further help! If there are a few employees who started to work with me on the same day, I need to get the one 3rd-oldest. — user3549835, Jun 03 '14 at 20:30

MySQL: Multiple table join

1 Answers1