Solved

query syntax (INNER JOIN)

Posted on 2016-07-25
18
67 Views
Last Modified: 2016-07-31
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 ?
0
Comment
Question by:amucinobluedot
  • 9
  • 8
18 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 41728618
MySQL does not have split function so you will havetowrite your own. You could find substring_index to be of use
0
 

Author Comment

by:amucinobluedot
ID: 41728622
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

0
 
LVL 40

Expert Comment

by:Sharath
ID: 41728626
Your SPLIT function syntax looks like SQL Server. Are you using SQL Server or MySQL?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:amucinobluedot
ID: 41728640
MS SQL 2008R2
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41728656
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
0
 

Author Comment

by:amucinobluedot
ID: 41728661
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

0
 
LVL 40

Expert Comment

by:Sharath
ID: 41728662
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.
0
 

Author Comment

by:amucinobluedot
ID: 41728663
Also, is there a way to use the split function I already have ?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41728666
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

0
 
LVL 40

Expert Comment

by:Sharath
ID: 41728667
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

0
 

Author Comment

by:amucinobluedot
ID: 41728668
I got this error now:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41728710
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.
0
 

Author Comment

by:amucinobluedot
ID: 41731114
I did and still get the same error. error
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41731301
You may have some unprintable characters like LF or CR in the script. Notepad++ may be helpful to get them.
0
 

Author Comment

by:amucinobluedot
ID: 41731304
That is what I used
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 41731315
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

0
 

Author Comment

by:amucinobluedot
ID: 41732045
Still get the same error  :$
Is there another way to split the numbers and join them to the admincatalog table ?
0
 

Author Comment

by:amucinobluedot
ID: 41736871
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now