We help IT Professionals succeed at work.

Avoid Duplicate in Left Join Query

Medium Priority
467 Views
Last Modified: 2012-05-11
SQL 2005 / 2008

I have Left Join Query.  I have the output in an Excel Sheet.

For Example :-

475      50836      INSULIN GLARGINE      00088222033
475      47780      INSULIN GLARGINE      00088222033
475      62867      INSULIN GLARGINE      00088222033


These values are generated from this query. What i am expecting is .. If the ID # is getting 3 or 2 rows, pick only the first row and ignore the remaining rows !! on this way, we can avoid duplicate issue !!!

Is it possible to do it in Join Query !!!
select	Distinct	PC.ID,
							Dr.GenericCode,
							PC.[Generic Name],
							PC.[NDC]
				from		PlacedOrderDetails PC
				Left Join	Drug Dr 
				on convert(varchar, Left(PC.NDC ,9),101) = convert(varchar, Left(case when Dr.DrugNDCType = 49 then Dr.DrugNDCNbr else stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6 when Dr.DrugNDCType = 52 then 10 end, 0, '0') end ,9),101)
				Order by	PC.ID ASC

Open in new window

GetGenericCode.xls
Comment
Watch Question

Top Expert 2011

Commented:
Please try the following:

select		PC.ID,
		MIN(Dr.GenericCode) GenericCode,
		PC.[Generic Name],
		PC.[NDC]
from		PlacedOrderDetails PC
Left Join	Drug Dr 
on convert(varchar, Left(PC.NDC ,9),101) = convert(varchar, Left(case when Dr.DrugNDCType = 49 then Dr.DrugNDCNbr else stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6 when Dr.DrugNDCType = 52 then 10 end, 0, '0') end ,9),101)
Group by	PC.ID, PC.[Generic Name], PC.[NDC]
Order by	PC.ID ASC

Open in new window

If you want to get first occurrence, You should have to use cursor. If minimal value is fine, Go ahead with the above solution.
chokkaStudent

Author

Commented:
Query results unique values .. but i am getting this message ..

Warning: Null value is eliminated by an aggregate or other SET operation.

chokkaStudent

Author

Commented:

Let me ask this way !!!

When there is a duplicate row, is there any way for us to pick the Max (GenericCode) and ignoring the remaining Generic Code associated with that specific id
Top Expert 2011

Commented:
Just use the MIN aggregate function as follows:

select		PC.ID,
		MAX(Dr.GenericCode) GenericCode,
		PC.[Generic Name],
		PC.[NDC]
from		PlacedOrderDetails PC
Left Join	Drug Dr 
on convert(varchar, Left(PC.NDC ,9),101) = convert(varchar, Left(case when Dr.DrugNDCType = 49 then Dr.DrugNDCNbr else stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6 when Dr.DrugNDCType = 52 then 10 end, 0, '0') end ,9),101)
Group by	PC.ID, PC.[Generic Name], PC.[NDC]
Order by	PC.ID ASC

Open in new window

Top Expert 2011

Commented:
Sorry, I meant to say MAX aggregate function.
chokkaStudent

Author

Commented:
wdosanjos: I feel that is not the correct syntax !! It returns , Warning !!

There is another way to do ..

using row_number() over Partition by ..

Let me get the correct syntax and share it with you
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
chokkaStudent

Author

Commented:
Have you come across with SQL Syntax : row_number over partition by

What it does is, internally go row by row and pick the max .. ! Instead of cursor .. we can use this some scenario's.

I dont have the proper syntax,

wdosanjos: I cant use your logic, due to Warning Exception.
chokkaStudent

Author

Commented:
Have to re open again !!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.