SQL Query - Specify which record to join to

Hi All,

I have a table of unique codes which has a one to many relationship with a table of statuses against these codes.

Most of the time, one code has one status, but it is possible to have two statuses for one code. When there are two statuses, I want to join to the status that does not start 'xxx'.

Thanks
LVL 12
James ElliottManaging DirectorAsked:
Who is Participating?
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show some data samples? maybe something to play with on sqlfiddle?
I have some ideas, but without having concrete data (and respective required output) it will rather be guesswork
just a couple of records
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
How about this (This assumes a "dbo.status" table with values "Status" and "Code":

with MyStatus1 as (
select *, row_number() over(partition by code order by case substring(Status,1,3) when 'xxx' then 1 else 0 end, status) as MyRow
from dbo.status),
MyStatus2 as
(Select * from MyStatus1 where MyRow = 1)
select *
from MyStatus2

Open in new window

0
James ElliottManaging DirectorAuthor Commented:
Ok, sorry for the vague question.

I have the following SQL which gives me a list of codes with status, in which there are duplicates. Basically, where there are two entries, I want to select the entry that does not start "Closed".

Thanks

SELECT
	a.Charge_Code,
	CASE WHEN d.Category_1 = 'CLOSED' AND a.Eligible = 1 THEN 'Closed - Accepted'
		 WHEN d.category_1 = 'CLOSED' AND a.Eligible = 0 THEN 'Closed - Rejected'
		 ELSE d.category_1
	END as status	
	
FROM
	tblRequestLines a
	
INNER JOIN
	tblStatusLines b
ON b.Request_Line_ID = a.ID

INNER JOIN
	tblStatuses c
ON b.Status_ID = c.ID

INNER JOIN
	tblStatusCategoryMap d
ON d.Status_ID = c.ID

WHERE b.End_Date IS NULL

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
With d1 as
(Select *, row_number() over(partition by d.Status_ID order by case substring(d.Category_1,1,6) when 'CLOSED' then 1 else 0 end, d.Category_1) as MyRow
FROM tblStatusCategoryMap d),
d2 as
(Select * from d1 where MyRow = 1)
SELECT
	a.Charge_Code,
	CASE WHEN d.Category_1 = 'CLOSED' AND a.Eligible = 1 THEN 'Closed - Accepted'
		 WHEN d.category_1 = 'CLOSED' AND a.Eligible = 0 THEN 'Closed - Rejected'
		 ELSE d.category_1
	END as status	
	
FROM
	tblRequestLines a
	
INNER JOIN
	tblStatusLines b
ON b.Request_Line_ID = a.ID

INNER JOIN
	tblStatuses c
ON b.Status_ID = c.ID

INNER JOIN
	d2 d
ON d.Status_ID = c.ID

WHERE b.End_Date IS NULL

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can do like this:

select * from ( SELECT
	a.Charge_Code,
	CASE WHEN d.Category_1 = 'CLOSED' AND a.Eligible = 1 THEN 'Closed - Accepted'
		 WHEN d.category_1 = 'CLOSED' AND a.Eligible = 0 THEN 'Closed - Rejected'
		 ELSE d.category_1
	END as status	
  , row_number() over (partition by a.Charge_Code order by case when d.Category_1 = 'CLOSED'  then 2 else 1 end) rn
	
FROM
	tblRequestLines a
	
INNER JOIN
	tblStatusLines b
ON b.Request_Line_ID = a.ID

INNER JOIN
	tblStatuses c
ON b.Status_ID = c.ID

INNER JOIN
	tblStatusCategoryMap d
ON d.Status_ID = c.ID

WHERE b.End_Date IS NULL
              ) sq
where sq.rn = 1                            

Open in new window

0

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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
along the same lines :)
0
James ElliottManaging DirectorAuthor Commented:
Thanks both. I went with Guy's solution in the end as it returned the correct number of records.
0
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 2005

From novice to tech pro — start learning today.