Home » SQL & PL/SQL » SQL & PL/SQL » Multiple tables joining
Multiple tables joining [message #670076] Mon, 04 June 2018 01:40 Go to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Hi All,

Could you please help me with sql query on my below request.

I have multiple tables to join and it's required to return row even if any one of the tables have the data.


CREATE TABLE MYTBL1 ( EMPLID VARCHAR(10), NAME VARCHAR(30), ERROR VARCHAR(1), DESCR VARCHAR (40));

INSERT INTO MYTBL1 VALUES ('12367', 'JOHN' , 'Y', 'INVALID NAME');

CREATE TABLE MYTBL2 ( EMPLID VARCHAR(10), NAME VARCHAR(30), ERROR VARCHAR(1), DESCR VARCHAR (40));

INSERT INTO MYTBL2 VALUES ('45378', 'PETER' , 'N', ' ');
By joining those 2 tables I should get the output since one of table has ERROR field has 'Y'. with standard join it will not return any row. And I'm not sure outer joining will resolve my issue. Please help me with sql. Thank You.

Regards
Suji
Re: Multiple tables joining [message #670077 is a reply to message #670076] Mon, 04 June 2018 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at full outer join in Join.

Re: Multiple tables joining [message #670082 is a reply to message #670077] Mon, 04 June 2018 04:34 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Hi Michel,

Thank you. I haven't try with full outer join.
I should get a row if any of my joining tables have ERROR filed value Y.

SELECT 'X' FROM MYTBL1
FULL OUTER JOIN MYTBL2 ON MYTBL1.EMPLID = MYTBL2.EMPLID
AND ((MYTBL1.ERROR = 'Y' AND MYTBL1.DESCR <> ' ') OR 
(MYTBL2.ERROR = 'Y' AND MYTBL2.DESCR <> ' ') )

hope the above sql query will return 'X' if any of the table have ERROR field has 'Y'.

Thank you.

Regards
Suji
Re: Multiple tables joining [message #670083 is a reply to message #670082] Mon, 04 June 2018 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is almost that:
SQL> SELECT nvl(MYTBL1.EMPLID, MYTBL2.EMPLID) EMPLID, 'X' FROM MYTBL1
  2  FULL OUTER JOIN MYTBL2 ON MYTBL1.EMPLID = MYTBL2.EMPLID
  3  where MYTBL1.ERROR = 'Y' OR MYTBL2.ERROR = 'Y'
  4  /
EMPLID     '
---------- -
12367      X
Re: Multiple tables joining [message #670084 is a reply to message #670083] Mon, 04 June 2018 06:43 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Thank you. But now I have concern like if I want to join 3rd MYTBL3 and this table has EROOR = 'Y'.
Now how should join all three tables. Please help me with sql query. Thank you.
Re: Multiple tables joining [message #670086 is a reply to message #670084] Mon, 04 June 2018 08:42 Go to previous message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Add the 3rd table in the same way.

Previous Topic: does execute immediate create a new connection?
Next Topic: Want to execute 900 query's, is there any faster way to execute
Goto Forum:
  


Current Time: Wed Jan 29 01:23:25 CST 2020