CODEWITHSUNDEEP
mysqljoin
Flemming Lemche
Flemming Lemche

Reputation: 181

Rewrite 2 select statements as one JOIN Statement

I've been strugling with a JOIN statement which I can't figure out.

I want to get the latest(newest) entry in two different tables and join the result in the output.

The 2 Select statements look like this and they work as expected but I can't seem to get the right result when I try to rewite them as a JOIN statement.

Please help, Thanks.

SELECT MaxNum 
FROM table1 
WHERE UserID = 4
ORDER BY Date DESC
LIMIT 1

SELECT MinNum 
FROM table2 
WHERE UserID = 4
ORDER BY Date DESC
LIMIT 1

Upvotes: 1

Views: 55

Answers (4)

Alexander
Alexander

Reputation: 4527

If you want to display result as one row then use your queries as subqueries in FROM clause.

SELECT *
FROM 
  (SELECT MaxNum FROM table1 WHERE UserID = 4 ORDER BY `Date` DESC LIMIT 1) AS q1,
  (SELECT MinNum FROM table2 WHERE UserID = 4 ORDER BY `Date` DESC LIMIT 1) AS q2;

Upvotes: 0

gabi13
gabi13

Reputation: 91

select TOP 1 table1.MaxNum, table2.MinNum 
FROM table1 INNER JOIN table2 
ON table1.UserID = table2.UserID 
WHERE table1.UserID = 4 
ORDER BY table1.Date DESC

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

I'm not sure that a join necessarily makes sense here. However, you can slightly modify what you currently have to be one query:

SELECT
    (SELECT MaxNum FROM table1 WHERE UserID = 4 ORDER BY Date DESC LIMIT 1) AS MaxNum,
    (SELECT MinNum FROM table2 WHERE UserID = 4 ORDER BY Date DESC LIMIT 1) AS MinNum

Upvotes: 1

Mureinik
Mureinik

Reputation: 310993

Since each of these queries only returns one row, one dirty trick you can use is to cross join the results:

SELECT MaxNum, MinNum
FROM   (SELECT   MaxNum 
        FROM     table1 
        WHERE    UserID = 4
        ORDER BY Date DESC
        LIMIT    1) t 
CROSS JOIN (SELECT   MinNum 
            FROM     table2 
            WHERE    UserID = 4
            ORDER BY Date DESC
            LIMIT    1) s

Upvotes: 0

Related Questions