Please help me to build select SQL and my criteria is
user
user_id deleted
111 1
222 0
333 1
tab1
user_id deleted
111 1
222 0
333 0
tab2
user_id deleted
111 1
222 0
333 1
tab3
user_id deleted
111 1
222 1
333 0
Situation for me is that if a user_id in user table is deleted (deleted=1) then all its reference records in tab1, tab2 and tab3 should be deleted (deleted=1).
I need to find out user_ids in user table who are deleted (deleted=1) but their records remained active in either tab1, tab2 or tab3 tables (deleted=0)
For example: user_id:111 is deleted and its data in all other tables are also deleted. But user_id:333 is deleted but its records in tab1 and tab3 remain active. I need to find out these user_id, like 333.
Please help me to build select sql
Try this:
WITH x AS (SELECT 111 AS user_id , 1 AS deleted FROM dual UNION ALL
SELECT 222 AS user_id, 0 AS deleted FROM dual UNION ALL
SELECT 333 AS user_id , 1 AS deleted FROM dual ),
y as (SELECT 111 AS user_id , 1 AS deleted FROM dual UNION ALL
SELECT 222 AS user_id, 0 AS deleted FROM dual UNION ALL
SELECT 333 AS user_id , 0 AS deleted FROM dual ),
z as (SELECT 111 AS user_id , 1 AS deleted FROM dual UNION ALL
SELECT 222 AS user_id, 0 AS deleted FROM dual UNION ALL
SELECT 333 AS user_id , 1 AS deleted FROM dual ),
w as (SELECT 111 AS user_id , 1 AS deleted FROM dual UNION ALL
SELECT 222 AS user_id, 1 AS deleted FROM dual UNION ALL
SELECT 333 AS user_id , 1 AS deleted FROM dual )
SELECT x.user_id FROM x, y, z, w
WHERE x.user_id = y.user_id AND
x.user_id = z.user_id AND
x.user_id = w.user_id AND
x.deleted = 1 AND
(x.deleted != y.deleted OR
x.deleted != z.deleted OR
x.deleted != w.deleted);
OUTPUT will be:
USER_ID
----------
333