I have this query that works however has one flaw. There are a few cases where on the left outer join there could be mulitple matches. All I want is to see one line per PO line for each time a match to a sales order was found. If there is more than one matching PO line, I only want to see that there was any match, no all of the matches. What it does now is that is one PO line had three matching sales order matches, the same PO line appears three times. I only want to see it once. Is is possible to restructure this query to only show it once?
DECLARE @AO bit
select pop.vendorid as VENDORID,pop.ponumber as PONUMBER,pop.itemnmbr AS ITEMNMBR,pop.qtyorder AS QTYORDER,pop.unitcost AS UNITCOST,pop.qtyorder*pop.unitcost as EXTENDEDCOST,(case when sop.ponumber is null then 1 else 0 end) as AO,sop.ord,pop.ord,sop.ponumber
from pop10110 pop
left outer join sop60100 sop on sop.ponumber = pop.ponumber and sop.ord=pop.ord
where (case when sop.ponumber is null then 1 else 0 end) = @AO
order by pop.vendorid,pop.ponumber,pop.itemnmbr