I would like to write an sql join query which retrieves the product number and supplier numbers of all unique pairs of suppliers who supply the same product, along with their product purchase prices.

Columns are:

SUPPLIER (SUPNR, SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS)

PRODUCT (PRODNR, PRODNAME, PRODTYPE, AVAILABLE_QUANTITY)

SUPPLIES (SUPNR, PRODNR, PURCHASE_PRICE, DELIV_PERIOD)

PURCHASE_ORDER (PONR, PODATE, SUPNR)

PO_LINE (PONR*,* PRODNR, QUANTITY)

NOTE: (primary keys are BOLD, foreign keys are bold in italics

my code is:

SELECT S1.SUPNR, S2.SUPNR, S2.PRODNR, S2.PURCHASE_PRICE
FROM SUPPLIES S1, SUPPLIES S2
WHERE S1.SUPNR > S2.SUPNR

Is it a correct query?

CodePudding user response：

I guess you just need to add PRODNR

 SELECT S1.SUPNR, S2.SUPNR, S2.PRODNR, 
  S2.PURCHASE_PRICE
  FROM SUPPLIES S1 join SUPPLIES S2
  On S1.SUPNR > S2.SUPNR and
  S1.Prodnr=s2.prodnr