Join another table 2 two times in a query


#1

t1
t1ID countryID cityID
1      2        3
2      1        4
3      4        1
4      3        2
t2
t2ID  country   city
1     France     Paris
2     Germany    Berlin
3     China      Peking
4     Japan      Tokyo

I have 2 tables like the above.
And I have the code below with $findID=2.

SELECT country
FROM t1 
LEFT JOIN t2 on t2ID=countryID
WHERE t1ID=$findID

And I have the code below with $findID=2.

SELECT city
FROM t1 
LEFT JOIN t2 on t2ID=cityID
WHERE t1ID=$findID

With the 2 SQLs above, I can get 2 result, i.e "France" and "Tokyo"
I like to make the 2 queries into 1 query for getting the same result "France" and "Tokyo".

The code below does not work correctly but I hope it shows what I want.

SELECT country, city
FROM (  
(SELECT country
FROM t1 
LEFT JOIN t2 on countryID=t2ID
WHERE t1ID=$findID)
UNION
(SELECT city
FROM t1 
LEFT JOIN t2 on cityID=t2ID
WHERE t1ID=$findID)
) as unionTable

#2

i guess you are new to sql and how union queries work?

each of the SELECTs in your union returns one column

those results are "concatenated" so that the result of your union is still one column although multiple rows

so you cannot really pull two columns out of a union result of one column


#3

I do well in LEFT JOIN but still confusing in MORE advanced SQL.

Do you mean I cannot get the result what I want with UNION or I can get the result what I want with UNION but I didn't use the UNION sql correctly?


#4

are there any online resources, such as tutorials, which you could consult in order to find out how union queries work?

union queries are not "advanced sql" so they should be covered in pretty much all basic, introductory tutorials

this google search for union queries returns 52,900,000 results!!

good luck :slight_smile:


#5

And therein lies your first problem. You need to first learn Database Normalization and fix your DB before you do anything.