Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage
query syntax (INNER JOIN)
I have a table that saves comma separated values, such as : 1,3,5
This values are the ID of records on a different table, called "Admintags"
When I add a record for a 'casecomment' one of the columns is called 'tags' and this is where I save those comma separated values.
I have the following query:
That will return the following values:
caseid | tags | comment
30638 | 1, 5 | Client will be traveling to Mexico City from Dec 15th until Jan 2nd.
What I need the query to return is the value of those tags from the 'tags' table. The tags table is very simple, it only has two columns
id = the ids saved above
tag_text = the text value of the tag, this is what I need in return:
30638 | Travel date, Billing | Client will be traveling to Mexico City from Dec 15th until Jan 2nd.
which are the corresponding values of 1 and 5 in the "Admintags" table.
How can I do an inner join to display those values instead of the ID's that I have in the casecomments table ?
This values are the ID of records on a different table, called "Admintags"
When I add a record for a 'casecomment' one of the columns is called 'tags' and this is where I save those comma separated values.
I have the following query:
SELECT
a.CaseID ,
a.tags,
a.Comments
FROM dbo.Casecomments a
That will return the following values:
caseid | tags | comment
30638 | 1, 5 | Client will be traveling to Mexico City from Dec 15th until Jan 2nd.
What I need the query to return is the value of those tags from the 'tags' table. The tags table is very simple, it only has two columns
id = the ids saved above
tag_text = the text value of the tag, this is what I need in return:
30638 | Travel date, Billing | Client will be traveling to Mexico City from Dec 15th until Jan 2nd.
which are the corresponding values of 1 and 5 in the "Admintags" table.
1 Travel Date
2 Agreement
3 Billing
4 Contact method
5 Email
How can I do an inner join to display those values instead of the ID's that I have in the casecomments table ?
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
LVL 35
MySQL does not have split function so you will havetowrite your own. You could find substring_index to be of use
I have a split function. I just don't know how to use it in this instance. Below is my function:
USE [BlueDot]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 07/25/2016 19:29:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[Split] (@list nvarchar(max), @delim nchar(1) = ',')
returns table as
return
with csvtbl(start, [stop]) as (
select start = convert(bigint, 1), [stop] = charindex(@delim collate slovenian_bin2, @list + @delim)
union all
select start = [stop] + 1, [stop] = charindex(@delim collate slovenian_bin2, @list + @delim, [stop] + 1)
from csvtbl
where [stop] > 0
)
select ltrim(rtrim(substring(@list, start, case when [stop] > 0 then [stop] - start else 0 end))) as value
from csvtbl
where ([stop] > 0)
and (ltrim(rtrim(substring(@list, start, case when [stop] > 0 then [stop] - start else 0 end))) <> '')
try this.
;WITH CTE AS (
SELECT a.CaseID ,
t.tag_text,
a.Comments
FROM Casecomments a
CROSS APPLY Split(tags,',') s
JOIN tags t ON s.value = t.id)
SELECT DISTINCT CaseID,
rtrim(substring(isnull((select ','+tag_text from CTE t2 where t1.CaseiD = t2.CaseID for xml path('')),' '),2,2000)) tags,
Comments
FROM CTE t1
Something may be wrong in the select, this is what I changed it to since the table name was incorrect.
I get this error:
WITH CTE
AS ( SELECT a.CaseID ,
t.tag_text ,
a.Comments
FROM Casecomments a
CROSS APPLY Split(tags, ',') s
JOIN dbo.AdminTags t ON s.value = t.id
)
SELECT DISTINCT
CaseID ,
RTRIM(SUBSTRING(ISNULL(( SELECT ',' + tag_text
FROM CTE t2
WHERE t1.CaseID = t2.CaseID
FOR
XML PATH('')
), ' '), 2, 2000)) tags ,
Comments
FROM CTE t1;
I get this error:
Msg 321, Level 15, State 1, Line 6
"tags" 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.
Is it not a column in your table, Casecomments? I am passing the column tags to your Split function to split it into multiple records.
I used your split function only. To be specific, you can use with 3-part name.
WITH CTE
AS ( SELECT a.CaseID ,
t.tag_text ,
a.Comments
FROM Casecomments a
CROSS APPLY BlueDot .dbo.Split(a.tags, ',') s
JOIN dbo.AdminTags t ON s.value = t.id
)
SELECT DISTINCT
CaseID ,
RTRIM(SUBSTRING(ISNULL(( SELECT ',' + tag_text
FROM CTE t2
WHERE t1.CaseID = t2.CaseID
FOR
XML PATH('')
), ' '), 2, 2000)) tags ,
Comments
FROM CTE t1;
like this
WITH CTE
AS ( SELECT a.CaseID ,
t.tag_text ,
a.Comments
FROM Casecomments a
CROSS APPLY BlueDot.dbo.Split(a.tags, ',') s
JOIN dbo.AdminTags t ON s.value = t.id
)
SELECT DISTINCT
CaseID ,
RTRIM(SUBSTRING(ISNULL(( SELECT ',' + tag_text
FROM CTE t2
WHERE t1.CaseID = t2.CaseID
FOR
XML PATH('')
), ' '), 2, 2000)) tags ,
Comments
FROM CTE t1;
In my SSMS, It didn't return any error. May be any invisible character. Can you paste in any text editor and copy from there.
You may have some unprintable characters like LF or CR in the script. Notepad++ may be helpful to get them.
Can you give a try on this.
;WITH CTE
AS (SELECT a.CaseID,
t.tag_text,
a.Comments
FROM Casecomments a
CROSS APPLY BlueDot.dbo.Split(a.tags, ',') s
JOIN dbo.AdminTags t
ON s.value = t.id)
SELECT DISTINCT CaseID,
Rtrim(Substring(Isnull((SELECT ',' + tag_text
FROM CTE t2
WHERE t1.CaseID = t2.CaseID
FOR XML PATH('')), ' '), 2, 2000)) tags,
Comments
FROM CTE t1;
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
Still get the same error :$
Is there another way to split the numbers and join them to the admincatalog table ?
Is there another way to split the numbers and join them to the admincatalog table ?
I decided to store the actual tags in text instead of the numbers so I don't have to do a join and split each value, I can simply display the text tags as they are in the database. I will split points. Thanks for trying.
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
Web Development
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.