Hello every one.
I have following data
Table 1:
ID LOC ST
1 30 12 gate
2 25 65 Jfk
3 45 45 Water
4 65 888 st
5 105 west st
5 110 north pole
6 999 wash. st
6 888 ind. ave
Table 2:
ID CT LOC ST
1 12 50 1 main
1 11 40 123 main
1 11 30 12 gate
1 12 30 12 gate
2 15 10 3 main
2 17 25 65 jfk
2 15 25 65 Jfk
3 25 45 45 Water
3 25 90 55 east st
3 100 90 55 east st
4 125 65 888 st
5 222 105 west st
5 333 110 north pole
6 55 999 wash. st
6 55 888 ind. ave
Expected result:
ID LOC ST OTHERSTCT OnlySt
1 30 12 gate Y N
2 25 65 Jfk N N
3 45 45 Water Y N
4 65 888 st N Y
5 105 west st N N
5 110 north pole N N
6 999 wash. st N N
6 888 ind. ave N N
I tried but the results are not correct, Also I am using same tables again and again.
WITH temp1 AS(
SELECT 1 ID,30 loc ,'12 gate' st FROM dual
UNION ALL
SELECT 2 ID,25 loc,'65 Jfk' st FROM dual
UNION ALL
SELECT 3 ID,45 loc,'45 Water' st FROM dual
UNION ALL
SELECT 4 ID,65 loc,'888 st' st FROM dual
UNION ALL
SELECT 5 ID,105 loc,'west st' st FROM dual
UNION ALL
SELECT 5 ID,110 loc,'north pole' st FROM dual
UNION ALL
SELECT 6 ID,999 loc,'wash. st' st FROM dual
UNION ALL
SELECT 6 ID,888 loc,'ind. ave' st FROM dual
),
temp2 AS (SELECT 1 ID,12 ct,50 loc,'1 main' st FROM dual
UNION ALL
SELECT 1 ID,11 ct,40 loc,'123 main' st FROM dual
UNION ALL
SELECT 1 ID,11 ct,30 loc,'12 gate' st FROM dual
UNION ALL
SELECT 1 ID,12 ct,30 loc ,'12 gate' st FROM dual
UNION ALL
SELECT 2 ID,15 ct,10 loc,'3 main' st FROM dual
UNION ALL
SELECT 2 ID,17 ct,25 loc,'65 jfk' st FROM dual
UNION ALL
SELECT 2 ID,15 ct,25 loc,'65 Jfk' st FROM dual
UNION ALL
SELECT 3 ID,25 ct,45 loc,'45 Water' st FROM dual
UNION ALL
SELECT 3 ID,25 ct,90 loc,'55 east st' st FROM dual
UNION ALL
SELECT 3 ID,100 ct,90 loc,'55 east st' st FROM dual
UNION ALL
SELECT 4 ID,125 ct,65 loc,'888 st' st FROM dual
UNION ALL
SELECT 5 ID,222 ct,105 loc,'west st' st FROM dual
UNION ALL
SELECT 5 ID,333 ct,110 loc,'north pole' st FROM dual
UNION ALL
SELECT 6 ID,55 ct,999 loc,'wash. st' st FROM dual
UNION ALL
SELECT 6 ID,55 ct,888 loc,'ind. ave' st FROM dual
)
SELECT temp1.*,
(CASE
WHEN temp1.ID = d.ID THEN 'N'
ELSE 'Y'
END) otherCtSt
FROM temp1,
(SELECT t2.ID,
t2.ct
FROM temp1 t1,
temp2 t2
WHERE T1.ID = t2.ID
AND t1.loc = t2.loc
MINUS
SELECT t2.ID,
t2.ct
FROM temp1 t1,
temp2 t2
WHERE T1.ID = t2.ID
AND t1.loc != t2.loc) d
WHERE temp1.ID = d.ID(+)
ID LOC ST OTHERCTST
1 30 12 gate Y
2 25 65 Jfk N
3 45 45 Water Y
4 65 888 st N
5 105 west st Y <<<<- this is wrong
5 110 north pole Y <<<<- this is wrong
6 999 wash. st Y <<<<- this is wrong
6 888 ind. ave Y <<<<- this is wrong
Flag: OtherCtSt Y and N
Flag:Onlyst means only distinct id and loc in table 2
Let me explain what I am looking for.
1. If the address in the table 1 is the only address in table 2 then OTHERSTCT =’N’ and OnlySt=’N’ example is id=4 and loc=65
2. Id and loc in table one has ct in table 2, If all ct in table 2 for that id and loc exists for same id and other loc in table 2 then flags Y and N. Example id=1
loc=30(table 1) and id=1 and loc(50,40) has same ct in table 2. Id=1,loc=30 in table 1 has ct 11,12, same ct 11,12 exists for id1 and loc(40,50) so the
flags shuld get Y and N (multiple id and loc in table 2)
3. falgs Y and N same as above example falls for id 3 and loc 45 in table 1 as id 3 and loc 45 has ct 25 in table 2 and same ct 25 also exists for id3 and other
loc 90
4. Id 2 gets flag N and N because id 2 and loc 25 has two ct (15,17) while id 2 has only one other ct 15 for loc 10 and no other loc or same loc has ct 17
so flag one gets N and flag2 gets N as multiple id and loc in table 2
5. Id 5 loc 105 and 110 should get N and N as id 5 and loc 105 has ct=222 there is no other loc which has same ct 222 and same applies for id=5 and loc
=110 as it has ct=333 and no other id and loc has ct=333. also Same no of record are in table 1 and table 2. Same id and same loc in table 1 and table
2 so both should get N N
6. id 6 , loc 999,888 should get N and N as Id 6 and loc 999 has ct 55 in table 2 ,but same ct 55 also exists for id 6 and loc 888 so should get N and N .
same for id 6 and loc 888, Same no of record are in table 1 and table 2. Same id and same loc in table 1 and table 2 so both should get N N
I hope I have explained it correctly. Please let me know if it is confusing.
Thank you in advance for your help.