Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Understanding a Join

User_VBJB2Nov 22 2022

Hello,
My actual table have similar situation where I am working on PO, RECEIPT and INVOICE but I have created below tables as sample as situation is same. The query gives me 4 rows however I am expecting only two rows returned by the query as both t2 and t3 has two rows for one row in t.
Can you please suggest what I am doing wring?

DROP TABLE t PURGE;

DROP TABLE t2 PURGE;

DROP TABLE t3 PURGE;

CREATE TABLE t (
  id NUMBER
);

CREATE TABLE t2 (
  pid NUMBER
  , id  NUMBER
);

CREATE TABLE t3 (
  pid NUMBER
  , id  NUMBER
);

INSERT INTO t
  (SELECT 1
   FROM dual
  );

INSERT INTO t2
  (SELECT 1
     , 1
   FROM dual
  );

INSERT INTO t2
  (SELECT 1
     , 2
   FROM dual
  );

INSERT INTO t3
  (SELECT 1
     , 1
   FROM dual
  );

INSERT INTO t3
  (SELECT 1
     , 2
   FROM dual
  );

SELECT *
 FROM t  t
   , t2 t2
   , t3 t3
 WHERE 1 = 1
  AND t.id  = t2.pid
  AND t.id  = t3.pid
AND t2.pid  = t3.pid;
Comments
Post Details
Added on Nov 22 2022
6 comments
139 views