CODEWITHSUNDEEP
sqlpostgresqlunion
Mahoni
Mahoni

Reputation: 7466

How to reference column of UNION

Given the query

SELECT t1.username, t1.email, t2.color FROM t1
UNION (SELECT * FROM t2)

Now in the first select I cannot reference t2, I get

missing FROM-clause entry for table "t2"`

So I assumed I need to alias the union select, as in:

UNION (SELECT * FROM t2) AS t

but I get syntax errors for this as well. What's the correct way to address this?

Example: http://sqlfiddle.com/#!9/96d5cf/3

Table t1: 

--username |    email
tom             [email protected]
alex            [email protected]

Table t2:

--color
black
blue

Expected result:

col1        col2...
value1      value2...
value11     value21...

Upvotes: 0

Views: 773

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

If i am not doing wrong, in order to get color value you need to define the relationship via primary key t1(username/userid) and foreign key t2(t1.username/t1.userid) between table t1 and t2. So, the query would be

select t1.username, t2.color 
from t1 
inner join t2 on t2.username/userid = t1.username/userid;

Upvotes: 1

Related Questions