SQL union question?


#1
SELECT ......

where u.id in (

SELECT userid  FROM
(select `entityId` as userid from table_a

union

select userid from table_b) g

This gives me an error! Column ‘userid’ in field list is ambiguous

I can’t seem to understand which column to use when employing union!

Will the bottom table get merged into the top one and I need to use the top table’s column as reference?

If I use SELECT * gives me an error about the fact that there can only be one column!

Please take a look at my query.

P.S. Should I use SELECT userid or SELECT entityId


#2

Try

SELECT ......
From ....
where u.id in (
    select `entityId`  from table_a
    union
    select userid from table_b
)

#3

this

if you could please show your entire query, i’m sure it can be simplified to remove the subquery


#4

Thanks brother! So it doesn’t matter whether I use SELECT userid or SELECT entityId ??


#5

thanks brother for trying to help but the query is super complex and for security reasons I can’t post the structure.


#6

of course it does, but without a better example, i’d only be guessing


#7

Every single time I have seen UNION on the help forums it was always a bad/improper DB design behind it. If that is the case, that is what needs to be addressed. Without seeing what you have we cant really help you.

You can always PM me your DB schema and I will review it.