[Webinar] Streamline your web hosting managementRegister Today

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

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
0
James Elliott
Asked:
James Elliott
  • 3
  • 2
  • 2
2 Solutions
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now