joining three tables in sql using union all

Question

CREATE TABLE ZIPCODE_16P2
(
ZIPCODE NUMBER (10) NOT NULL,
CITY    VARCHAR2 (50) NOT NULL,
STATE   VARCHAR2 (50) NOT NULL,
PRIMARY KEY (ZIPCODE)
);


CREATE TABLE REGISTER_LSP_16P2
(
LAB_ID               NUMBER (10) NOT NULL,
PASSWORD             VARCHAR2 (255) NOT NULL,
LAB_NAME             VARCHAR2 (255) NOT NULL,
ADDRESS              VARCHAR2 (255) NOT NULL,
ZIPCODE              NUMBER (10) NOT NULL,
CONTACT_NUMBER       NUMBER (10) NOT NULL,
ALTERNATE_CONTACT_NO NUMBER (10) NOT NULL,
EMAIL                VARCHAR2 (255) NOT NULL,
CATEGORY             VARCHAR2 (255) NOT NULL,
SECURITY_QUESTION    VARCHAR2 (255) NOT NULL,
SECURITY_ANSWER      VARCHAR2 (255) NOT NULL,
PRIMARY KEY (LAB_ID),
FOREIGN KEY (ZIPCODE) REFERENCES ZIPCODE_16P2 (ZIPCODE),
UNIQUE (PASSWORD),
UNIQUE (CONTACT_NUMBER),
UNIQUE (ALTERNATE_CONTACT_NO),
UNIQUE (EMAIL)
);



CREATE TABLE TBL_TEST_786
(
TEST_CODE        NUMBER (30),
LAB_ID           NUMBER (30),
TEST_NAME        VARCHAR2 (255),
TEST_DESCRIPTION VARCHAR2 (255),
TEST_DURATION    NUMBER (30),
COST_OF_THE_TEST NUMBER (30),
LAB_HOME         VARCHAR2 (255)
);

I got three tables with me i want to join them to get required fields LAB_NAME,ADDRESS, ZIPCODE, STATE,CITY,TEST_NAME, TEST_CODE, TEST_DESCRIPTION, COST_OF_THE_TEST, LAB_HOME. I tried joining with inner join and not getting the result can you please help me with this.

Please show us what code you have tried, some sample data and expected outcome — Bernd Linde, Jul 28 '15 at 04:55
SELECT LAB_NAME,ADDRESS,ZIPCODE,STATE,CITY,TEST_NAME,TEST_CODE,TEST_DESCRIPTION,COST_OF_THE_TEST,LAB_HOME FROM REGISTER_LSP_16P2,ZIPCODE_16P2,TBL_TEST_786 WHERE REGISTER_LSP_16P2.ZIPCODE=ZIPCODE_16P2.ZIPCODE AND REGISTER_LSP_16P2.LAB_ID=TBL_TEST_786.LAB_ID — Vignesh kumar K, Jul 28 '15 at 04:56
that is the query i have tried and it is ORA-00918 column ambiguously defined — Vignesh kumar K, Jul 28 '15 at 04:57
Update your question with the code (Use the "Edit link") and format it. It is difficult to read it in a comment and any future readers will not need to read the comments to get the additional information — Bernd Linde, Jul 28 '15 at 05:00

score 0 · Answer 1 · answered Jul 28 '15 at 05:34

Try this

SELECT LAB_NAME,ADDRESS,REGISTER_LSP_16P2.ZIPCODE,STATE,CITY,TEST_NAME,TEST_CODE,TEST_DESCRIPTION,COST_OF_THE_TEST,LAB_HOME FROM
 REGISTER_LSP_16P2 join ZIPCODE_16P2 on REGISTER_LSP_16P2.ZIPCODE=ZIPCODE_16P2.ZIPCODE join TBL_TEST_786 on REGISTER_LSP_16P2.LAB_ID=TBL_TEST_786.LAB_ID

joining three tables in sql using union all

1 Answers1