help with SQL JOIN 1

[wysiwygGER01]

Hi,

My SQL statement returns a few duplicate lines and I can't figure out how to improve it.

I narrowed my problem down to the join between my view "PO" and my table pmtran.

The view "PO" looks like this and returns:

SELECT porcpl.ponumber,porcph1.vdname, porcpl.venditemno
FROM porcpl
INNER JOIN porcph1 ON porcph1.ponumber = porcpl.ponumber
WHERE porcpl.ccategory = '110' AND PORCPL.PONUMBER like 'PO003306'

ponumber | vdname | venditemno
10 | testvendor | 123
10 | testvendor | 456

This is what I want to see. This is correct.


My final statement looks like this and returns the following:

SELECT PM.CATEGORY, PM.RESOURCE, pm.TRANSQTY, po.vendditemno
FROM PMTRAN AS PM
LEFT JOIN PO ON PM.refdoc = po.ponumber
AND PM.CATEGORY = '110'
WHERE PM.fmtcontno = 'P-09-001-00'
AND PM.project = '120'
AND PM.costrev = '1'
AND PM.doctype IN ('7', '8', '9', '17', '18')
ORDER BY PM.RESOURCE;

category | resource | quantity | vendditemno
100 | 98 | 1 | NULL
100 | 99 | 3 | NULL
110 | NULL | 1 | 123
110 | NULL | 1 | 456<---this line is
redundant
110 | NULL | 2 | 123<---this line is
redundant
110 | NULL | 2 | 456

What I'd like it to return is:

category | resource | quantity | vendditemno
100 | 98 | 1 | NULL
100 | 99 | 3 | NULL
110 | NULL | 1 | 123
110 | NULL | 2 | 456

Can this be done at all?

 

[r937]

try it like this --

SELECT PM.CATEGORY
     , PM.RESOURCE
     , PM.TRANSQTY
     , PO.vendditemno
  FROM PMTRAN AS PM
LEFT OUTER
  JOIN PO ON PM.refdoc = po.ponumber
 [red]WHERE[/red] PM.CATEGORY = '110'
   AND PM.fmtcontno = 'P-09-001-00'
   AND PM.project = '120'
   AND PM.costrev = '1'
   AND PM.doctype IN ('7', '8', '9', '17', '18')
ORDER 
    BY PM.RESOURCE

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[wysiwygGER01]

Hi r937,

Thanks for your post but the result is still the same.
Still shows the redundant rows.

 

[WoodyGuthrie]

Not sure why you would want to choose one quantity on an item over another quantity on the same item, so I would try something like this:

SELECT PM.CATEGORY
     , PM.RESOURCE
     , SUM(PM.TRANSQTY)
     , PO.vendditemno

FROM PMTRAN AS PM

LEFT JOIN PO 
ON PM.refdoc = po.ponumber

WHERE PM.CATEGORY = '110'
   AND PM.fmtcontno = 'P-09-001-00'
   AND PM.project = '120'
   AND PM.costrev = '1'
   AND PM.doctype IN ('7', '8', '9', '17', '18')

GROUP BY
  PM.CATEGORY
, PM.RESOURCE
, po.vendditemno

Woody

 

[MakeItSo]

Hi WysiwygGer01,

the problem is the different quantity which makes these rows not redundant from a simple "select" point of view.
Do you wish to skip these records or - what might be more sensible - to group & sum?

Try with SUM(pm.TRANSQTY), GROUP BY ..., and HAVING ... instead of WHERE...

Cheers,
MiS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

Ah, woody was a tad faster...


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[wysiwygGER01]

Hi,

thank you for your replies. I can't simply use SUM or group them because these 2 rows are completely wrong.
I have added the field quantity to my view PO and added another condition in my final statement. It appears to be working now:
Final version:
View PO:

SELECT porcpl.ponumber,porcph1.vdname, porcpl.venditemno, porcpl.itemdesc, PORCPL.RQRECEIVED
FROM porcpl
INNER JOIN porcph1 ON porcph1.ponumber = porcpl.ponumber
WHERE porcpl.ccategory = '110'


final statement:

SELECT PM.CATEGORY, PM.RESOURCE, PM.TRANSQTY, PM.icuom,
po.venditemno, po.itemdesc, po.vdname,
PM.docnum, PM.comments, pm.REFDOC, po.ponumber
FROM PMTRAN AS PM
LEFT JOIN PO ON PM.refdoc = po.ponumber
AND PM.CATEGORY IN ('110')
AND PM.TRANSQTY = po.RQRECEIVED
WHERE PM.fmtcontno = 'P-09-001-00'
AND PM.project = '120'
AND PM.costrev = '1'
AND PM.doctype IN ('7', '8', '9', '17', '18')
ORDER BY PM.RESOURCE;

Thanks again!

PS: Which Syntax Highlighter are your guys using to have your code looking like this?

 

[markros]

http://www.tek-tips.com/viewthread.cfm?qid=1350140&page=1

 

[MakeItSo]

Thanks markros & especially gmmastros!


I must admit, I downloaded that two years ago and totally forgot about it again!

Just to test the output:

[COLOR=blue]SELECT[/color] PM.CATEGORY, PM.RESOURCE, PM.TRANSQTY, PM.icuom, po.venditemno, po.itemdesc, po.vdname, PM.docnum, PM.comments, pm.REFDOC, po.ponumber
[COLOR=blue]FROM[/color] PMTRAN [COLOR=blue]AS[/color] PM
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] PO [COLOR=blue]ON[/color] PM.refdoc = po.ponumber
     AND PM.CATEGORY IN ([COLOR=red]'110'[/color])
     AND PM.TRANSQTY = po.RQRECEIVED
[COLOR=blue]WHERE[/color] PM.fmtcontno = [COLOR=red]'P-09-001-00'[/color]
   AND PM.project = [COLOR=red]'120'[/color]
   AND PM.costrev = [COLOR=red]'1'[/color]
   AND PM.doctype IN ([COLOR=red]'7'[/color], [COLOR=red]'8'[/color], [COLOR=red]'9'[/color], [COLOR=red]'17'[/color], [COLOR=red]'18'[/color])
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] PM.RESOURCE;

Yepp, looks nice!!


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell