CODEWITHSUNDEEP
sqlpostgresql
PloniStacker
PloniStacker

Reputation: 666

SQL - How to get values from multiple tables without being ambiguous

Apologies if this question had been asked before (it probably did). I never used SQL before and the answers I've got only got me more confused.

I need to find out if an ID exists on different tables and get the total number from all tables.

Here is my query:

select * from public.ui1, public.ui2, public.ui3 where id = '123'

So if id 123 doesn't exist in ui1 and ui2 but does exist in ui3, I'd still like to get it. (I would obviously like to get it if it exists in the other tables)

I am currently getting an ambiguous error message as id exists in all tables but I am not sure how to construct this query in the appropriate manner. I tried join but failed miserably. Any help on how to reconstruct it and a stupid proof explanation would be highly appreciated!

EDIT: What I would finally like to find out is if id = 123 exists in any of the tables.

Upvotes: 1

Views: 242

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

What I would finally like to find out is if id = 123 exists in any of the tables.

The best way to do this is probably just using exists:

select v.id, 
       (exists (select 1 from public.ui1 t where t.id = v.id) or
        exists (select 1 from public.ui2 t where t.id = v.id) or
        exists (select 1 from public.ui3 t where t.id = v.id)
       ) as exists_flag
from (values (123)) v(id);

As written, this returns one row per id defined in values(), along with a flag of whether or not the id exists -- the question you are asking.

This can easily be tweaked if you want additional information, such as which tables the id exists in, or the number of times each appears.

Upvotes: 1

user330315
user330315

Reputation:

It's a bit unclear what the result is you expect. If you want the count then you can use a UNION ALL

select 'ui1' as source_table, 
       count(*) as num_rows
from public.ui1
where id = 123
union all
select 'ui2', 
       count(*)
from public.ui2
where id = 123
union all
select 'ui3', 
       count(*)
from public.ui3
where id = 123

If you only want to know if the id exists in at least one of the tables (so a true/false) result you can use:

select exists (select id from ui1 where id = 123
               union all 
               select id from ui2 where id = 123
               union all
               select id from ui3 where id = 123)

Upvotes: 3

Related Questions