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

rwheeler23Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rwheeler23Connect With a Mentor Author 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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
All Courses

From novice to tech pro — start learning today.