query syntax (INNER JOIN)

Aleks
Aleks used Ask the Experts™
on
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:

 SELECT  
			a.CaseID ,
            a.tags,
            a.Comments
          
    FROM    dbo.Casecomments a

Open in new window


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 

Open in new window


How can I do an inner join to display those values instead of the ID's that I have in the casecomments table ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
MySQL does not have split function so you will havetowrite your own. You could find substring_index to be of use

Author

Commented:
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))) <> '')

Open in new window

SharathData Engineer

Commented:
Your SPLIT function syntax looks like SQL Server. Are you using SQL Server or MySQL?
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Author

Commented:
MS SQL 2008R2
SharathData Engineer

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

Author

Commented:
Something may be wrong in the select, this is what I changed it to since the table name was incorrect.

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; 

Open in new window


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.

Open in new window

SharathData Engineer

Commented:
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.

Author

Commented:
Also, is there a way to use the split function I already have ?
SharathData Engineer

Commented:
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; 

Open in new window

SharathData Engineer

Commented:
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; 

Open in new window

Author

Commented:
I got this error now:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.
SharathData Engineer

Commented:
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.

Author

Commented:
I did and still get the same error. error
SharathData Engineer

Commented:
You may have some unprintable characters like LF or CR in the script. Notepad++ may be helpful to get them.

Author

Commented:
That is what I used
Data Engineer
Commented:
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; 

Open in new window

Author

Commented:
Still get the same error  :$
Is there another way to split the numbers and join them to the admincatalog table ?

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial