Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join with Multiple IDs to multiple descriptions

Author  Topic 

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-21 : 12:05:19
Hello,

I am having an issue with showing data when there are multiple IDs that I am joining. When there are multiple IDs SQL Server has an issue putting them in a field. Here is my code that is erroring out...


SELECT [CFSID]
,[RID]
,[ActionTakenIDs]
,cd.Description

FROM ComplaintReport cr
LEFT JOIN CombinedDesc cd ON cd.ID = cr.ActionTakenIDs


Here is what it looks like if I don't add the description. On CFSID 192173 where there is multiple ID fields it fails. When it is like CFSID 73290 with only one it works like a charm.


CFSID RID ActionTakenIDs
73290 NULL 1604
152320 128 1586
192173 NULL 1256,1582,1260


Here is what it looks like if there is only one actionTakenIDs

CFSID RID ActionTakenIDs Description
73290 NULL 1604 Traffic


Here is what I want it to look like:

CFSID RID ActionTakenIDs Description
73290 NULL 1604 Traffic
192173 NULL 1256,1582,1260 Park, Beach, Coconut

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-21 : 15:27:59
Is the CombinedDesc table normalized? Can you post the sample data in that table as well?

This would be almost trivial if the ComplaintReport (and CombinedDesc) were in 1NF. Because ComplaintReport is not, even assuming CombinedDesc is normalized, one would have to parse the comma-separated string, join to the CombinedDesc table and then concatenate the results.

In any case, please post the sample data in CombinedDesc table.
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-21 : 15:35:59
Here is the sample data from CombinedDesc


ID Description
1256 Park
1260 Coconut
1582 Beach


Yeah, this is a clients former database design. We changed their database design we are just trying to get the data out now and then I ran into this crap.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-21 : 15:56:32
Since you have not posted readily consumable data, it is hard to test, but here is my attempt at doing this. You need a function that can split a comma-separated string into individual pieces. My example below uses the function that Jeff Moden has posted in this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153458 You can use others as well, just change the function name and the parameter names as appropriate.

;with cte1 as
(
SELECT
a.cfsid,
a.rid,
a.ActionTakenIDs,
b.ItemNumber,
b.Item
FROM
ComplaintReport a
CROSS APPLY dbo.DelimitedSplit8K(ActionTakenIDs,',') b
)
SELECT
c1.cfsid,
c1.rid,
c1.ActionTakenIDs,
STUFF(b.Descriptions,1,1,'')
FROM
cte1 c1
CROSS APPLY
(
SELECT b.Description
FROM CombinedDesc b
WHERE b.ActionTakenId = c1.Item
FOR XML PATH('')
)b(Descriptions);
Not sure if this will preserve the ordering of the comma-separated tokens when reconstituted.
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-22 : 10:30:23
For some reason after I have ran the function then I run the script you posted it returns this error:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '.'.

Any ideas? There aren't any random periods or anything either.


;with cte1 as
(
SELECT
a.cfsid
,a.ReportOfficerID
,a.ActionTakenIDs
,b.ItemNumber
,b.Item
FROM [NewClient].[dbo].[ComplaintReport] a
CROSS APPLY dbo.DelimitedSplit8K(a.ActionTakenIDs,',') b
)
SELECT
c1.cfsid,
c1.ReportOfficerID,
c1.ActionTakenIDs,
STUFF(b.Descriptions,1,1,'')
FROM
cte1 c1
CROSS APPLY
(
SELECT b.Description
FROM newclient.dbo.CombinedDesc b
WHERE b.ID = c1.Item
FOR XML PATH('')
)b(Descriptions);
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 10:54:48
Not sure why. I assume you are on SQL 2008. If you are on a version earlier than SQL 2005, CROSS APPLY wouldn't work. Also, can you verify that the function has been installed using this query in your database? It should return one row if it is installed
SELECT * FROM sys.objects WHERE OBJECT_NAME(OBJECT_ID) = 'DelimitedSplit8K'
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-22 : 10:58:06
Yes, I am on 2008 r2... Yes, it returned one row.


name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
DelimitedSplit8K 1956970098 NULL 1 0 IF SQL_INLINE_TABLE_VALUED_FUNCTION 2013-01-22 09:05:51.160 2013-01-22 09:05:51.160 0 0 0
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-22 : 11:14:47
Also, I ran both of these queries to make sure there weren't any random punctuation in the data and neither returned any rows...

SELECT *
FROM [NewClient].[dbo].[ComplaintReport]
where ActionTakenIDs = '%.%'

SELECT *
FROM [NewClient].[dbo].[CombinedDesc]
where Description = '%.%'
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-22 : 11:31:45
Sorry this has been such a pain in the arse James and sticking out this far into it... Definitely appreciate the help, mate.

It is somewhere in this code. I played around with commenting a bit and it has something to do with the cross apply part of it.

Runs:

SELECT
a.cfsid
,a.ReportOfficerID
,a.ActionTakenIDs
--,b.ItemNumber
--,b.Item
FROM [NewClient].[dbo].[ComplaintReport] a
--CROSS APPLY dbo.DelimitedSplit8K(a.ActionTakenIDs,',') b


Fails:

SELECT
a.cfsid
,a.ReportOfficerID
,a.ActionTakenIDs
,b.ItemNumber
,b.Item
FROM [NewClient].[dbo].[ComplaintReport] a
CROSS APPLY dbo.DelimitedSplit8K(a.ActionTakenIDs,',') b

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 11:41:22
I created some test data and made changes to the code. (If you post readily consumable test data like I have in the code below, that makes it easier) You can copy and paste this code to run it
-- Consumable test data
CREATE TABLE #ComplaintReport
(CFSID INT, RID INT, ActionTakenIDs VARCHAR(32));
CREATE TABLE #CombinedDesc (id INT, [Description] VARCHAR(32));

INSERT INTO #ComplaintReport VALUES
(73290, NULL, '1604'),
(152320, 128, '1586'),
(192173 ,NULL,'1256,1582,1260')

INSERT INTO #CombinedDesc VALUES
(1256,'Park'),
(1260,'Coconut'),
(1582,'Beach'),
(1604,'Traffic'),
(1586,'Weather');

-- Query
;with cte1 as
(
SELECT
a.cfsid,
a.rid,
a.ActionTakenIDs,
b.ItemNumber,
b.Item,
d.[Description]
FROM
#ComplaintReport a
CROSS APPLY master.dbo.DelimitedSplit8K(ActionTakenIDs,',') b
INNER JOIN #CombinedDesc d ON d.id = b.Item
)
SELECT
c1.cfsid,
c1.rid,
c1.ActionTakenIDs,
STUFF(b.Descriptions,1,1,'')
FROM
(SELECT DISTINCT cfsid, rid, ActionTakenIds FROM cte1) AS c1
CROSS APPLY
(
SELECT ','+b.[Description]
FROM cte1 b
WHERE b.cfsid = c1.cfsid
FOR XML PATH('')
)b(Descriptions);


DROP TABLE #ComplaintReport;
DROP TABLE #CombinedDesc;
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-22 : 12:02:20
James,

Can't thank you enough. I figured it out... I was getting an error saying that it didn't recognize ActionTakenIDs on this line

CROSS APPLY master.dbo.DelimitedSplit8K(ActionTakenIDs,',') b

So I changed it to this:
CROSS APPLY master.dbo.DelimitedSplit8K(a.ActionTakenIDs,',') b

That was the period that threw it off. I removed the a. and get this error again...

'"ActionTakenIDs" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.'

Did a google search a ran this code:
ALTER DATABASE NewClient
SET COMPATIBILITY_LEVEL = 100

Now it works like a champ... Thanks again for all your help and patience!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 12:57:22
You are very welcome - glad to be of help.

When you change the compatibility level, you need to be careful, because some of the features that work in a lower compatibility level may have been discontinued. Go to this page to see if you were relying on any such features http://technet.microsoft.com/en-us/library/cc707782(v=sql.100).aspx
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-22 : 14:43:35
Gotcha, it shouldn't be too much of a problem because once the data is out, the database will be deleted anyway...

I know I am asking too much again but I am fairly new/ never used with clauses before so I am getting some errors when putting it into my insert/select script.

How would I insert something like that into this where it will insert the data from the portion that was converted into the string? I tried putting it all in parenthesis and then in the select portion take out the select c1.cfsid, c1.ReportOfficerID,c1.ActionTakenIDs, and just leve the STUFF(b.Descriptions,1,1,'') but SQL is getting mad at the with statement.

Here is a similar conversion script to what I will be using:


INSERT INTO [CAD].[dbo].[CADNOTE]
(
INCIDENTNO
,NOTE
)

SELECT
cConv.INCIDENTNO AS INCIDENTNO
,--How would I get that code that you wrote into this with it just bring over that specific data?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 15:09:51
The insert into clause should come just before the final select - something like this:
;with cte1 as
(
SELECT
a.cfsid,
a.rid,
a.ActionTakenIDs,
b.ItemNumber,
b.Item,
d.[Description]
FROM
#ComplaintReport a
CROSS APPLY master.dbo.DelimitedSplit8K(ActionTakenIDs,',') b
INNER JOIN #CombinedDesc d ON d.id = b.Item
)
INSERT INTO [CAD].[dbo].[CADNOTE]
(
INCIDENTNO
,NOTE
)
SELECT
c1.cfsid,
--c1.rid,
--c1.ActionTakenIDs,
STUFF(b.Descriptions,1,1,'')
FROM
(SELECT DISTINCT cfsid, rid, ActionTakenIds FROM cte1) AS c1
CROSS APPLY
(
SELECT ','+b.[Description]
FROM cte1 b
WHERE b.cfsid = c1.cfsid
FOR XML PATH('')
)b(Descriptions);
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-22 : 16:54:42
Thanks again for everything man. It works great and I am finally able to get the last pieces for this data transfer. If you ever need someone to look over any of your scripts need a hand give me a shout. I am still learning a lot but once I learn something once I won't have to ask again. Haha.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 17:52:21
You are quite welcome - glad to be of help.

And, thank you for the offer; I will remember it and will surely take you up on it when the day comes!
Go to Top of Page
   

- Advertisement -