CODEWITHSUNDEEP
phpmysql
joseagaleanoc
joseagaleanoc

Reputation: 615

How can I select the lowest number from multiple tables in MySQL?

Lets say I have three tables:
Table1
Id  Score
1     2
2     5
3     5

Table2
Id  Score
1     1
2     2
3     3

Table3
Id  Score
1     4
2     4
3     2

How can I get the lowest value for each Id from the three tables? So when I execute the query it would post something like this:

Id   LowestScore
1       1
2       2
3       2

Thanks!!

Upvotes: 0

Views: 123

Answers (1)

John Woo
John Woo

Reputation: 263723

You can simply use UNION to combine the records from the three tables and wrap it on a subquery then use MIN() to get the lowest score for each ID.

SELECT  ID, MIN(Score) Score
FROM    
        (
            SELECT  ID, Score FROM Table1
            UNION ALL
            SELECT  ID, Score FROM Table2
            UNION ALL
            SELECT  ID, Score FROM Table3
        ) sub
GROUP   BY ID

OUTPUT

╔════╦═══════╗
║ ID ║ SCORE ║
╠════╬═══════╣
║  1 ║     1 ║
║  2 ║     2 ║
║  3 ║     2 ║
╚════╩═══════╝

Upvotes: 4

Related Questions