CODEWITHSUNDEEP
mysqljoin
Sky
Sky

Reputation: 121

SELECT INNER JOIN SELECT by in MYSQL

The table:

|A    |    |B          |    |C                               |
|-----|    |-----|-----|    |-----|-----|-----------|--------|
|AKey |    |BKey |AKey |    |CKey |BKey |Date       |Result  |
|-----|    |-----|-----|    |-----|-----|-----------|--------|
|1    |    |1    |1    |    |101  |1    |2016-12-01 |F       |
|     |    |2    |1    |    |102  |1    |2016-12-01 |F       |
|     |    |3    |1    |    |103  |3    |2016-12-01 |P       |
|     |    |     |     |    |104  |1    |2016-12-01 |P       |

I had tried this SQL command:

SELECT a.Akey, b.BKey, c.CKey, Date, Result
FROM A AS a
INNER JOIN B as b ON a.AKey=b.AKey
LEFT JOIN (SELECT * FROM C WHERE Date="2016-12-01"
ORDER BY CKey DESC
LIMIT 0, 1) AS c ON c.BKey=b.BKey
WHERE a.AKey=1

I got the result as follows:

|AKey |BKey |CKey |Date       |Result  |
|-----|-----|-----|-----------|--------|
|1    |1    |104  |2016-12-01 |P       |
|1    |2    |NULL |NULL       |NULL    |
|1    |3    |NULL |NULL       |NULL    |



But, How to get result as follows?

|AKey |BKey |CKey |Date       |Result  |
|-----|-----|-----|-----------|--------|
|1    |1    |101  |2016-12-01 |P       |
|1    |2    |NULL |NULL       |NULL    |
|1    |3    |103  |2016-12-01 |P       |

Upvotes: 2

Views: 4393

Answers (2)

sagi
sagi

Reputation: 40481

Your inner query always select the same row. You can fetch the latest record for each Akey with NOT EXISTS() :

SELECT a.Akey, b.BKey, c.CKey, Date, Result 
FROM A AS a
INNER JOIN B as b ON a.AKey=b.AKey 
LEFT JOIN (SELECT * 
            FROM C 
            WHERE Date="2016-12-01" 
             AND NOT EXISTS(SELECT 1 FROM c t1
                            WHERE t1.cid > c.cid 
                              and t1.bkey = c.bkey) ) AS c 
ON c.BKey=b.BKey 
WHERE a.AKey=1

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You probably want something like this:

SELECT a.Akey, b.BKey, c2.CKey, c2.Date, c2.Result 
FROM A AS a
INNER JOIN B as b ON a.AKey=b.AKey 
LEFT JOIN (
   SELECT MIN(CKey) AS min_CKey, BKey  
   FROM C 
   WHERE Date = "2016-12-01" 
   GROUP BY BKey
) AS c ON c.BKey = b.BKey 
LEFT JOIN C AS c2 ON c.BKey = c2.BKey AND c.min_CKey = c2.CKey
WHERE a.AKey=1 AND c2.Date = "2016-12-01"

You have to use a GROUP BY clause in order to get the minimum CKey value per BKey. Then join to C table onve more in order to get the rest of the fields.

Upvotes: 3

Related Questions