Link to home
Start Free TrialLog in
Avatar of Star79
Star79Flag for United States of America

asked on

SQL-SubQuery to remove duplicates

Hello,
I have the below query:
select * from expeditetbl1 ex 
left outer join exp_dummytbl expd on ex.program=expd.pgm_codes
 where
 ex.open_qty <>0
and vendor_code='HAL601' and po_num='41309844'
and emp_num='092'

select * from exp_dummytbl where emp_num='092' and pGM_CODES='661'

Open in new window


The second query has duplicates for that combination so when I do the left join in the first query its returning 2 rows.How can I modify the first query to a subquery so that the duplicates are eliminated.
Thanks.
ASKER CERTIFIED SOLUTION
Avatar of vr6r
vr6r

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Racim BOUDJAKDJI
Use DISTINCT or specify a scheme by which you consider a row a duplicate of another.

Hope this helps
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Outer Apply is Awesome!  That is a great example.
I'm not opposed to the distribution of points, just curious....

Can you post an explanation/what your end solution was?  I think that it MIGHT have depended on what you were actually wanting.
Avatar of Star79

ASKER

The first answer gave me the right results but I also noticed the others worked as well.