Correct SQL Syntax Needed

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

Open in new window

LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Umar Topia.Net Full Stack DeveloperCommented:
You can use DISTINCT to avoid having same rows multiple times
0
Aneesh RetnakaranDatabase AdministratorCommented:
that could be because of the multiple records on one of those tables. please paste the sample output and the deside one
0
Rajkumar GsSoftware EngineerCommented:
If you SELECT any columns from second table also, which have more matching records, DUPLICATE will not work.

Can you please paste query in main comment area?

Since I am from mobile, can't read code-snipplet fully

Raj
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Rajkumar GsSoftware EngineerCommented:
Sorry :)

...  records, DISTINCT will not work...

Raj
0
Rajkumar GsSoftware EngineerCommented:
Try this way... Not sure
(Since I cannot see your actual query posted in code-snipplet, an example here)

SELECT * FROM T1 LEFT OUTER JOIN (SELECT TOP 1 * FROM T2 WHERE ID = T1.ID)

Raj
0
rwheeler23Author Commented:
I correct my code and found that this corrects the issue:

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
where ponumber in ( select ponumber from sop60100 where ponumber=pop.ponumber)
order by pop.vendorid,pop.ponumber,pop.itemnmbr
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.