Search code examples
mysqlsqloledb

maximum value from three different tables

I have three tables which are as:

  • table1(ID,JName, XVal, YVal)
  • table2(ID, RName, XVAl, YVal)
  • table3(ID, TName, XValue, YValue)

XValue, YValue , XVal, YVal are of number type. I want maximum and minimum value of XVal and YVal among all these tables. Previously I wrote following queries:

Select max(XVal) FROM table1
Select max(XVal) FROM table2
Select max(XValue) FROM table3

And for minimum value:

Select min(XVal) FROM table1
Select min(XVal) FROM table2
Select min(XValue) FROM table3

By running these queries, I can get 3 different results for minimum and maximum and then, I can just use logical codes to compare between them and get the maximum and minimum value but what i want here is single query which will do this for me.
I have used following queries:

Select max(XVal) from table1 UNION select max(XVal) from table2 UNION select max(XValue) as XVal from table3 ORDER BY 'MAX(XVal)'DESC LIMIT 1

And another one:

select max(XVal) as maxval from(select XVal from table1 UNION ALL SELECT XVal from table2 UNION ALL Select XValue from table3) as subquery

But both of them failed. Can somebody help me with this? And also point out the cause for the above queries to not function?

Solution

  • The correct syntax for the first of the two versions is:

    Select max(XVal) as max_XVal from table1 UNION ALL
select max(XVal) from table2 UNION ALL
select max(XValue) as XVal from table3
ORDER BY max_XVal DESC
LIMIT 1;

    You can use either UNION or UNION ALL here.

    If you want the minimum and maximum values from the three tables:

    SELECT MIN(XVal), MAX(XVal)
FROM
(
    SELECT XVal FROM table1
    UNION ALL
    SELECT XVal FROM table2
    UNION ALL
    SELECT XVal FROM table3
) t123;