I have three tables called table a, table b and table c all the three tables have one common column.

How to get the common maximum value from the three tables?

here is my table info:

table a

id  salary
101  10000
102  15000
103  18000

table b

id salary
110  14000
127  21000
118  15000

table c

id  salary
191  15000
192  20000
193   8000

my required output is :

salary

15000

CodePudding user response：

Use UNION ALL (or UNION) to get the salaries of all 3 tables with an extra column, like an id, which marks the source table and then in the HAVING clause of an aggregation query check if that salary belongs to all tables.
Finally use ORDER BY with LIMIT 1 to get the max common salary:

SELECT salary
FROM (
  SELECT 1 id, salary FROM tableA UNION ALL
  SELECT 2 id, salary FROM tableB UNION ALL
  SELECT 3 id, salary FROM tableC 
) t
GROUP BY salary
HAVING COUNT(DISTINCT id) = 3 -- get only the common salaries
ORDER BY salary DESC LIMIT 1;

CodePudding user response：

You can use inner joins between the tables to make sure you're only considering values that occur in all three tables.

Then use MAX() to get the greatest of such values.

SELECT MAX(salary) AS salary
FROM tableA JOIN tableB USING (salary) JOIN tableC USING (salary)