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;