CODEWITHSUNDEEP
sqloracle-database
chester
chester

Reputation: 38

SQL check if a row of table A exists in tables B or C

The following tables names are just an example...

Imagine that I have a table USER, a table SUB_USER_1 and a table SUB_USER_2. The tables SUB_USER_1(2) have a column ID and a column USER_ID, that connects them to the USERS table.

Now if I wanted to know if a user is of type 1 or 2, checking if it exists in table SUB_USER_1 or SUB_USER_2, what was the best way to do it?

Thanks in advance.

Upvotes: 0

Views: 685

Answers (2)

Abecee
Abecee

Reputation: 2393

SELECT 'SUB_USER_1' whichTable FROM SUB_USER_1 WHERE user_id = 999
UNION ALL
SELECT 'SUB_USER_2' FROM SUB_USER_2 WHERE user_id = 999;

will tell you, which table holds the user 999's data.

If you wanted to work with the users data and the Sub_User tables have compatible columns, you may

SELECT AllSub.*, U.*
FROM
  (SELECT user_id, column1 alias1, column2 alias2, ... FROM Sub_User1
  UNION ALL
  SELECT user_id, column3, column4, ... FROM Sub_User1) AllSub
JOIN User U
  ON AllSub.user_id = U.id;

Could narrow down to specific user_id in the sub-select or outside, if required.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I would do this using a case in the select:

select (case when su1.id is not null and su2.id is not null then 'BOTH'
             when su1.id is not null then 'TYPE1'
             when su2.id is not null then 'TYPE2'
             else 'Neither'
        end)
from (select 999 as user_id from dual) u left join
     sub_user_1 su1
     on su1.id = u.user_id left join
     sub_user_2 su2
     on su2.id = u.user_id

This assumes that there are not duplicate rows in the sub type tables. That can be handled using aggregation.

Upvotes: 2

Related Questions