left outer join of 3 tables [message #665470] |
Wed, 06 September 2017 12:52 |
|
suji6281
Messages: 135 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 #665501 is a reply to message #665493] |
Thu, 07 September 2017 10:44 |
Solomon Yakobson
Messages: 3275 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.
|
|
|
|
|