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[/code]
I have 2 tables like the above.
And I have the code below with $findID=2.
[code]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
[quote=“r937, post:2, topic:301564”]
i guess you are new to sql and how union queries work?
[/quote]I do well in LEFT JOIN but still confusing in MORE advanced SQL.
[quote=“r937, post:2, topic:301564”]
so you cannot really pull two columns out of a union result of one column
[/quote]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?
