JOIN 2 columns with same name but from different tables with condition from another column-CodePudding

I need to get every value from 2 columns with same names in different tables. All of that with a match condition from another column

//TABLE1
 city  | code |
here   | 123  |
there  | 567  |
another| 498  |

//TABLE2
 city  | code |
here   | 813  |
there  | 379  |
another| 111  |

Expected result from city "there"

| 567 |
| 379 |

I tried many possibilities with JOIN and UNION but i can find the right way

CodePudding user response：

I you need to vertically join table, you can use UNION

SELECT code FROM table1 WHERE city = 'there'
UNION
SELECT code FROM table2 WHERE city = 'there'

If ou have more than a small amount of taböe, you need to aggregate with cahe or if clause

CodePudding user response：

I suggest you these 2 requests, you can see the result in this link: http://sqlfiddle.com/#!9/ded8d6/9

(SELECT code FROM TABLE1 WHERE city = 'there')
UNION
(SELECT code FROM TABLE2 WHERE city = 'there');


SELECT code FROM 
  (
      (SELECT * FROM TABLE1)
      UNION
      (SELECT * FROM TABLE2)
   ) T
WHERE T.city = 'there';

CodePudding user response：

I would like to suggest to use anyone from the following query and you can see the result in this link : [http://sqlfiddle.com/#!9/ded8d6/13][1]

1.

    SELECT code FROM table1 WHERE city = 'there'
    UNION
    SELECT code FROM table2 WHERE city = 'there' 

2. 

    SELECT code FROM table1 WHERE city = 'there'
    UNION ALL
    SELECT code FROM table2 WHERE city = 'there'

The only difference between Union and Union All is that Union extracts the rows that are being specified in the query while Union All extracts all the rows including the duplicates (repeated values) from both the queries.