[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

Avoid Duplicate in Left Join Query

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
0
chokka
Asked:
chokka
  • 5
  • 4
1 Solution
 
wdosanjosCommented:
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

0
 
radcaesarCommented:
If you want to get first occurrence, You should have to use cursor. If minimal value is fine, Go ahead with the above solution.
0
 
chokkaAuthor Commented:
Query results unique values .. but i am getting this message ..

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

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
chokkaAuthor 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
0
 
wdosanjosCommented:
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

0
 
wdosanjosCommented:
Sorry, I meant to say MAX aggregate function.
0
 
chokkaAuthor 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
0
 
wdosanjosCommented:
That's just a warning.  You can eliminate it with the following:

select		PC.ID,
		MAX(ISNULL(Dr.GenericCode,0)) 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

0
 
chokkaAuthor 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.
0
 
chokkaAuthor Commented:
Have to re open again !!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now