CODEWITHSUNDEEP
oracle-databasejoin
Cyrallia
Cyrallia

Reputation: 37

Oracle SQL Convert to Join Operator Style

The query works, but I was asked to rewrite it in join operator style.

SELECT pr.ProdName, pr.ProdPrice
FROM Product pr, OrderTbl ord, OrdLine ol, Customer cu
WHERE pr.ProdNo=ol.ProdNo
AND ord.OrdNo=ol.OrdNo
AND cu.CustNo=ord.CustNo
AND cu.CustNo='C2388597'
AND ord.OrdDate BETWEEN '01-Jan-04' AND '31-Jan-04';

I've just started out using Oracle, so I'm not sure how. I think I should be using INNER JOINS, but I don't know exactly how. Could someone help me, please?

Upvotes: 0

Views: 162

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

SELECT pr.ProdName, pr.ProdPrice
FROM Product pr
 INNER JOIN OrdLine ol ON pr.ProdNo=ol.ProdNo
 INNER JOIN OrderTbl ord ON ord.OrdNo = ol.OrdNo
 INNER JOIN Customer cu ON cu.CustNo=ord.CustNo
WHERE cu.CustNo='C2388597'
AND ord.OrdDate BETWEEN '01-Jan-04' AND '31-Jan-04';

Here is for you to read on Joins:.

Upvotes: 0

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

You should definitly read about joins though, but here is how this cross join style can be converted to inner join

SELECT pr.ProdName, pr.ProdPrice
FROM Product pr
 INNER JOIN OrdLine ol ON pr.ProdNo=ol.ProdNo
 INNER JOIN OrderTbl ord ON ord.OrdNo = ol.OrdNo
 INNER JOIN Customer cu ON cu.CustNo=ord.CustNo 
WHERE cu.CustNo='C2388597'
AND ord.OrdDate BETWEEN '01-Jan-04' AND '31-Jan-04';

Upvotes: 1

Related Questions