Home » SQL & PL/SQL » SQL & PL/SQL » left outer join of 3 tables
left outer join of 3 tables [message #665470] Wed, 06 September 2017 12:52 Go to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
I have 3 tables as shown below.

CREATE TABLE VENDOR_TBL (
    VendorID varchar(10),
    VendorName varchar(55),
    Address varchar(255),
    City varchar(15) 
);

CREATE TABLE VENDOR_TYPE (
    VendorID varchar(10),
    VendorName varchar(55),
    VendorType varchar(3),
    VendorLoc varchar(5)
);

CREATE TABLE TYPE_DESCR (
    FieldName varchar(10),
    FieldValue varchar(3),
    FieldLongDescr varchar(255)
);

INSERT INTO VENDOR_TBL (VendorID, VendorName, Address, City) VALUES ('V1', 'V1Name', 'addr1', 'houston');
INSERT INTO VENDOR_TBL (VendorID, VendorName, Address, City) VALUES ('V2', 'V2Name', 'addr2', 'houston');
INSERT INTO VENDOR_TBL (VendorID, VendorName, Address, City) VALUES ('V3', 'V3Name', 'addr3', 'houston');

INSERT INTO VENDOR_TYPE (VendorID, VendorName, VendorType, VendorLoc) VALUES ('V1', 'V1Name', 'CON', 'houston');
INSERT INTO VENDOR_TYPE (VendorID, VendorName, VendorType, VendorLoc) VALUES ('V2', 'V2Name', 'TMP', 'houston');

INSERT INTO TYPE_DESCR (FieldName, FieldValue, FieldLongDescr) VALUES ('VendorType', 'CON', 'Contract');
INSERT INTO TYPE_DESCR (FieldName, FieldValue, FieldLongDescr) VALUES ('VendorType', 'TMP', 'Temporary');
INSERT INTO TYPE_DESCR (FieldName, FieldValue, FieldLongDescr) VALUES ('VendorType', 'ABS', 'Absolute');


I'm excepting output should be as below.
VendorId     VendorType       FieldLongDescr
V1              CON             Contract
V2              TMP             Temporary
V3

I have tried VENDOR_TBL with left outer join VENDOR_TYPE and Inner Join with TYPE_DESCR table, but getting only 2 rows.
I should get 3 rows as i am joining with VENDOR_TBL.
Please help me with SQL.

Thank You.


Regards
Sekhar
Re: left outer join of 3 tables [message #665471 is a reply to message #665470] Wed, 06 September 2017 13:36 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
It looks like you need
VENDOR_TBL with left outer join VENDOR_TYPE and left Join with TYPE_DESCR table,
Re: left outer join of 3 tables [message #665472 is a reply to message #665471] Wed, 06 September 2017 13:57 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Yes pablolee.

By joining these 3 tables i should get all rows of VENDOR_TBL and respective column values from other 2 tables. thank you
Re: left outer join of 3 tables [message #665491 is a reply to message #665472] Thu, 07 September 2017 06:55 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
I've tried with below SQL but showing only 2 rows. But output should be with 3 rows.
select a.vendor_id, b.vendor_type, c.fieldLongDescr
from vendor_tbl a left outer join vendor_type b on 
a.vendorid = b.vendorid and
a.vendorname = b.vendorname
inner join type_descr c on c.FieldName = 'VendorType' and 
c.FieldValue = b.Vendortype
output of above SQL as follows:
VendorId     VendorType       FieldLongDescr
V1              CON             Contract
V2              TMP             Temporary

But output should be something as below:
VendorId     VendorType       FieldLongDescr
V1              CON             Contract
V2              TMP             Temporary
V3

Please suggest me. Thank You.

Regards
Sekhar
Re: left outer join of 3 tables [message #665492 is a reply to message #665471] Thu, 07 September 2017 07:19 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
pablolee wrote on Wed, 06 September 2017 19:36
It looks like you need
VENDOR_TBL with left outer join VENDOR_TYPE and left Join with TYPE_DESCR table,
Re: left outer join of 3 tables [message #665493 is a reply to message #665492] Thu, 07 September 2017 07:34 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
yes, but getting only 2 rows. suppose to get all rows of VENDOR_TBL.
Re: left outer join of 3 tables [message #665497 is a reply to message #665493] Thu, 07 September 2017 08:14 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I highlighted part of the quote in bold for a reason.
Re: left outer join of 3 tables [message #665501 is a reply to message #665493] Thu, 07 September 2017 10:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Native notation:

SQL> select  t1.vendorid,
  2          t2.vendortype,
  3          t3.fieldlongdescr
  4    from  vendor_tbl t1,
  5          vendor_type t2,
  6          type_descr t3
  7    where t2.vendorid(+) = t1.vendorid
  8      and t3.fieldname(+) = 'VendorType'
  9      and t3.fieldvalue(+) = t2.vendortype
 10  /

VENDORID   VEN FIELDLONGDESCR
---------- --- --------------------
V1         CON Contract
V2         TMP Temporary
V3

SQL> 

SY.
Re: left outer join of 3 tables [message #665506 is a reply to message #665493] Thu, 07 September 2017 12:16 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
You have not followed what was suggested (as CM has implied)
Re: left outer join of 3 tables [message #665527 is a reply to message #665506] Fri, 08 September 2017 07:22 Go to previous message
suji6281
Messages: 134
Registered: September 2014
Senior Member
thank You CM. It's working fine now, after adding 'Left Join' with 2nd and 3rd table.
Previous Topic: Why some of my SQL are run slower in Oracle In-memory than it was before?
Next Topic: UNIQUE CONSTRAINT FOR EXISTING COLUMN WITH DUPLICATE RECORDS
Goto Forum:
  


Current Time: Fri Mar 29 09:43:26 CDT 2024