CODEWITHSUNDEEP
sqloracle-databasejoin
Deepan
Deepan

Reputation: 127

Oracle SQL Join query clarification

I have a clarification to make wrt Oracle SQL Joins.

Scenario:

I have two tables.

T1 has the following structure

cId, pId, uId, catId, catName, tId, tName, tEvent

& T2 has the following structure

cId, uId, pId

PS: cId, uId and pId are common columns

I would like a sqL statement which would provide me the following

1) All records on T1 with or without match in T2

2) Records on T2 which are not in T1.

Ex:-

T1:

cId     pId     uId     catId       catName     tId     tName   tEvent

1001    2010    dolp      11         Amber      1100     Rod     Login
1001    2014    dolp      11         Amber      1100     Rod     Login
1002    2010    dolp      13         Anders     1500     Oli     Login
1003    2010    dolp      14         Rock       1501     Ret     Login
1004    2010    dolp      15         Lila       1600     Win     Login

T2:

cId     pId     uId

1001    2010    dolp
1001    2015    dolp

Query should provide the following result:

cId     pId     uId     catId       catName     tId     tName   tEvent

1001    2010    dolp      11         Amber      1100     Rod     Login 
{Record in T1}
1001    2014    dolp      11         Amber      1100     Rod     Login {Record in T1}
1002    2010    dolp      13         Anders     1500     Oli     Login {Record in T1}
1003    2010    dolp      14         Rock       1501     Ret     Login {Record in T1}
1004    2010    dolp      15         Lila       1600     Win     Login {Record in T1}
1001    2015    dolp      11         Amber      1100     Rod     Login {Record in T2}

An help on this is much appreciated.

Thanks.

Upvotes: 0

Views: 108

Answers (2)

abrittaf
abrittaf

Reputation: 537

1) All records on T1 with or without match in T2

SELECT T1.*
FROM T1 LEFT OUTER JOIN T2 ON T2.cId=T1.cId 
                          AND T2.uId=T1.uId 
                          AND T2.pId=T1.uId
/

2) Records on T2 which are not in T1.

SELECT T2.*
FROM   T2
WHERE NOT EXISTS 
             (SELECT 1 
              FROM   T1 
              WHERE  T1.cId=T2.cId 
                AND  T1.uId=T2.uId 
                AND  T1.pId=T2.uId)
/

Upvotes: 2

Sud
Sud

Reputation: 153

Use a left outer join where T2 is the 1st table. Reference below for understanding joins.

select a.cId,a.pId,a.uId,b.catId,b.catName,b.tId,b.tName,b.tEvent from T2 a right outer join T1 b
on a.cId = b.cId and a.pId =b.pId ;

Refer: http://www.techonthenet.com/oracle/joins.php

Upvotes: 0

Related Questions