SQL - Conditional Join Query

SQL 2005 / 2008

I have SQL QUery and its ouput attached in this Question.

The query runs on a Left Join Condition ..
Which picks First 9 digit of NDC on either side and results ..!

From the result, we came across few rows for Generic Code has NULL Value it ..!
In order to fix or update the NULL Value, we need to run an Update Left join Query with new condition ... By picking First 10 digits of NDC on either Side.

The second condition has to run only if i got NULL Values on Generic Code for those specific rows ..!

How should i have two conditional join .. with select and update ..
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
chokkaStudentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Brendt HessSenior DBACommented:
As I am understanding your question:  The output for the above query returns some items with a NULL Dr.GenericCode value.  These are cases where the entry in the Drug table is missing.  This, however, leads to a bit of confusion on my part.  You state:

    "In order to fix or update the NULL Value, we need to run an Update Left join Query with new condition ... By picking First 10 digits of NDC on either Side"

From this, I assume that the Drug table has entries that need to be modified?  Or did you mean that you needed to add entries?  I'm just not certain what table is being updated, with what value, for what record.  An example of data and expected results for this data modification would be very helpful.
radcaesarCommented:
Dont messup your null update with this join.

1. Use a transaction.

2. Create an ALIAS table (Inmemory) to hold your outupt with null.

3. Run an update on the alias table to update the null.

3. Close the Transaction.

Also, What is the volume of data in your tables, Is is too high?
cojdevCommented:
Do you really want to join on the first 10 if the value is null. Or do you want to join on less characters of 8.

I have attached aquery that might work based on what you have stated. The query uses a second left join and the result is only used if the first value is null.
select	Distinct	
	PC.ID,
	COALESCE(Dr.GenericCode, Dr2.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)
Left Join	Drug Dr2 on convert(varchar, Left(PC.NDC ,10),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 ,10),101)
Order by	PC.ID ASC

Open in new window

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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

chokkaStudentAuthor Commented:
cojdev: Thank you.  

Current Syntax results duplication .. ( I am getting duplicate even before .. just because of Left Join )

Id                   Generic Code  Generic Name                   NDC
475      47780      INSULIN GLARGINE      00088222033
475      50836      INSULIN GLARGINE      00088222033
475      62867      INSULIN GLARGINE      00088222033


Whether it is possible for us to tune the your syntax in such a way to pick MAX(Generic Code) by using
 row_number() over by partition ...!

On doing so, i think we can eliminate duplicate Id's
cojdevCommented:
Does this work to remove the dupes?
select	Distinct	
	PC.ID,
	MAX(COALESCE(Dr.GenericCode, Dr2.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)
Left Join	Drug Dr2 on convert(varchar, Left(PC.NDC ,10),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 ,10),101)
Order by	PC.ID ASC 
group by PC.ID, 	PC.[Generic Name],	PC.[NDC]

Open in new window

chokkaStudentAuthor Commented:
Cojdev, i have opened a seperate thread. In this thread, i have clarified my issue.
Thank you
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 2008

From novice to tech pro — start learning today.