SQL Join

[johnny45]

Trying to match Purchace order VS Item pricing to verify our margins.....

This works fine:

SELECT POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORL.OQORDERED, POPORL.UNITCOST
FROM (POPORL INNER JOIN POPORH1 ON POPORL.PORHSEQ = POPORH1.PORHSEQ) INNER JOIN ICITEM ON POPORL.ITEMNO = ICITEM.ITEMNO
WHERE (((POPORH1.PONUMBER)="003786") AND ((ICITEM.STOCKITEM)<>0))
ORDER BY POPORH1.PONUMBER, POPORL.ITEMNO;

Now I wanted to bring in the contract pricing for this as follows:

SELECT POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORL.OQORDERED, POPORL.UNITCOST, ICCUPR.FIXPRICE, Format(ICCUPR.FIXPRICE/POPORL.UNITCOST/100,"#0.00 %") AS ['%'], ICCUPR.CUSTNO
FROM ((POPORL INNER JOIN POPORH1 ON POPORL.PORHSEQ = POPORH1.PORHSEQ) INNER JOIN ICITEM ON POPORL.ITEMNO = ICITEM.ITEMNO) LEFT JOIN ICCUPR ON POPORL.ITEMNO = ICCUPR.ITEMNO
WHERE (((POPORH1.PONUMBER)="003786") AND ((ICCUPR.CUSTNO)="25013") AND ((ICITEM.STOCKITEM)<>0))
ORDER BY POPORH1.PONUMBER, POPORL.ITEMNO;

this does NOt return all items on or PO if there is NO contract pricing...I though a left join would return ALL items on PO and put NULLS were there is NO contyract pricing...??

 

[Jay Converse]

Probably because you've got a function that references a field in ICCUPR.

 

[johnny45]

Thanks Tuba,
I tried removing the function and still got the same results....

 

[Jay Converse]

You've also got ICCUPR.CUSTNO="25013

 

[johnny45]

Woudl I need to use a HAVING clause to get this to work ?

 

[Jay Converse]

I would try

(ICCUPR.CUSTNO="25013" or ICCUPR.CUSTNO is null)

 

[johnny45]

Thank you tuba2007.

Now I get back more rows but NOT all rows on PO.

I figured out that the omited rows are rows where there IS a contract price but with a different customer number, so this would be the tweak needed to get all PO rows with the contract pricing by a specific customer number or blank pricing if there is no contract pricing for that customer number.....
I tried this but I think there is error with my syntax:

SELECT POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORL.OQORDERED, POPORL.UNITCOST, ICCUPR.FIXPRICE, Format(ICCUPR.FIXPRICE/POPORL.UNITCOST/100,"#0.00 %") AS ['%'], ICCUPR.CUSTNO
FROM ((POPORL INNER JOIN POPORH1 ON POPORL.PORHSEQ = POPORH1.PORHSEQ) INNER JOIN ICITEM ON POPORL.ITEMNO = ICITEM.ITEMNO) LEFT JOIN ICCUPR ON POPORL.ITEMNO = ICCUPR.ITEMNO
WHERE (((POPORH1.PONUMBER)="003786") AND ((ICCUPR.CUSTNO)="25013" Or (ICCUPR.CUSTNO) Is Null OR( ICCUPR.FIXPRICE is null AND ((ICCUPR.CUSTNO)="25013")) AND ((ICITEM.STOCKITEM)<>0))
ORDER BY POPORH1.PONUMBER, POPORL.ITEMNO;