CODEWITHSUNDEEP
mysqljoin
Federico Sanchez
Federico Sanchez

Reputation: 145

Join 2 non existing tables in MYSQL

I'm having trouble joining two tables that come from two queries, meaning I don't have the actual table in my database. I have the following:

SELECT
    fiscalYear,
    BidOwner,
    count(cbid) AS c
FROM
    financials_tbl
GROUP BY
    BidOwner,
    fiscalYear

that one brings each BidOwner for each year with the amounts of bids he/she did that year.

Now this one:

SELECT
    fiscalYear,
    max(c)
FROM
    (
        SELECT
            fiscalYear,
            BidOwner,
            count(cbid) AS c
        FROM
            financials_tbl
        GROUP BY
            BidOwner,
            fiscalYear
    ) InputTable
GROUP BY
    fiscalYear

brings the maximum amount of bids done by an employee that year (2 columns).

What I need is to join this last table with the corresponding bid owner from the previous one, like a vlookup to find who scored the max amount of deals each year shown in the second table.

Upvotes: 0

Views: 78

Answers (2)

piotrgajow
piotrgajow

Reputation: 2950

You can join the results in such way:

SELECT
t2.fiscalYear,
t2.BidOwner,
t2.c
FROM (
    SELECT
        fiscalYear,
        max(c) as max
    FROM
        (
            SELECT
                fiscalYear,
                BidOwner,
                count(cbid) AS c
            FROM
                financials_tbl
            GROUP BY
                BidOwner,
                fiscalYear
        ) InputTable
    GROUP BY
        fiscalYear
) t1
JOIN (
    SELECT
        fiscalYear,
        BidOwner,
        count(cbid) AS c
    FROM
        financials_tbl
    GROUP BY
        BidOwner,
        fiscalYear  
) t2
ON  t2.max = t1.c
AND t2.fiscalYear = t1.fiscalYear;

However performance of such query will not be nice for large data sets...

Upvotes: 0

Don't Panic
Don't Panic

Reputation: 41810

It looks like you are trying to find a groupwise maximum on the result of your count query. Unfortunately, I do not know a way to do this without creating two of the same subquery, but I think this should work.

SELECT
    t1.fiscalYear, t1.BidOwner, t1.c
FROM
    (SELECT fiscalYear, BidOwner, count(cbid) AS c
     FROM financials_tbl
     GROUP BY BidOwner, fiscalYear) t1
LEFT JOIN
    (SELECT fiscalYear, BidOwner, count(cbid) AS c
     FROM financials_tbl
     GROUP BY BidOwner, fiscalYear) t2
ON t1.fiscalYear = t2.fiscalYear AND t1.c < t2.c
WHERE t2.BidOwner IS NULL

Upvotes: 2

Related Questions