CODEWITHSUNDEEP
sqloracle-database
Thesystem32
Thesystem32

Reputation: 17

How to Join two tables on two separate variables with one variable having the same name

This is what I have so far. I'm trying to join these two tables and if I do not use a using() function, then SQL tells me there is an invalid relational operator just simply using an ON. However using a "using" also throws an error that I did not end it properly. Any help would be nice, thanks.

DROP TABLE JOINED_TABLE;
CREATE TABLE JOINED_TABLE AS

SELECT *
FROM
H_SAMPLE50 H
INNER JOIN D_SAMPLE50 D
USING(ID_NBR)
AND(H.PULL_DATE = D.PREV_DATE)

Upvotes: 0

Views: 303

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You have two choices. One is where:

SELECT *
FROM H_SAMPLE50 H INNER JOIN
     D_SAMPLE50 D
     USING (ID_NBR)
WHERE H.PULL_DATE = D.PREV_DATE;

The other is ON:

SELECT *
FROM H_SAMPLE50 H INNER JOIN
     D_SAMPLE50 D
     ON H.ID_NBR = D.ID_NBR AND H.PULL_DATE = D.PREV_DATE;

Think of USING as a short-hand to handle the case where all the JOIN keys have the same name. If this is not the case, then just use ON.

Upvotes: 1

Related Questions