Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).
Course Of The Month
6 days, 12 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
query syntax (INNER JOIN)
Posted on 2016-07-25
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 ?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
9
8
18 Comments
MySQL does not have split function so you will havetowrite your own. You could find substring_index to be of use
Active 4 days ago
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))) <> '')
Active today
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
Active 4 days ago
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.
Active today
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.
Active today
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;
Active today
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;
Active 4 days ago
I got this error now:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.
Active today
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.
Active today
You may have some unprintable characters like LF or CR in the script. Notepad++ may be helpful to get them.
Active today
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;
Featured Post
Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
When it comes to write a
Context Sensitive Help
(an online help that is obtained from a specific point in state of software to provide help with that state)
,
first we need to make the file that contains all topics, which are given exclusive IDs. …
Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them? Continue reading to learn how to make your images …
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments.
What Bitcoin is:
Legality:
Risks:
Benefits:
Which businesses are best suited?:
Other things you should know:
How to get started:
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month6 days, 12 hours left to enroll
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.