CODEWITHSUNDEEP
phpmysql
Jasir alwafaa
Jasir alwafaa

Reputation: 586

Join tables and find maximum of column value

I have 3 tables table_1,table_2 and table_3 having common column comm_name as foreign key.i just want to find max of column data_id by joining these 3 tables. using where condition comm_name

in short: union these three tables and find maximum data_id , ie: return 9

I tried like:

SELECT max(data_id) FROM (( SELECT table_1.data_id FROM table_1 where comm_name='aa') UNION(SELECT table_2.data_id FROM table_2 where comm_name='aa') UNION(SELECT table_3.data_id FROM table_2 where comm_name='aa'));

But its showing error

An expression was expected. (near "(" at position 26)
Unexpected token. (near "(" at position 26)
Unexpected token. (near "(" at position 27)
This type of clause was previously parsed. (near "SELECT" at position 29)
This type of clause was previously parsed. (near "SELECT" at position 125)
This type of clause was previously parsed. (near "SELECT" at position 220)

enter image description here

Upvotes: 3

Views: 64

Answers (2)

Harshad Hirapara
Harshad Hirapara

Reputation: 462

Try This

SELECT Name, MAX(data_id) as MaxId
    FROM
    (
        SELECT data_id
        FROM table1
        UNION ALL
       SELECT data_id
        FROM table2
        UNION ALL
       SELECT data_id
        FROM table3
    );

Upvotes: 0

ka_lin
ka_lin

Reputation: 9432

Union operator should help you to have 1 data set which you can query (not tested): 

select max(data_id) from (select data_id from table_1 union select data_id from table_2 union select data_id from table_3)

Upvotes: 2

Related Questions