Results 1 to 11 of 11

Thread: table joining

  1. #1
    Join Date
    May 2012
    Posts
    6

    Unhappy Unanswered: table joining

    I have 3 tables I'm trying to join:
    Table 1: Contains ID, Name, Description
    Table 2: Contains ID, Keyword
    Table 3: Is lookup table for tables 1 & 2

    I need a query to return a table having columns of:
    Name, Description, Keyword for every record ID in Table 1

    The issue is that table 2 contains multiple keywords for each key ID and my query returns an error: Subquery returned more than 1 value.


    When I run this query using a specific key, it returns the correct information the way I want it:



    DECLARE @ServiceID VARCHAR(10)
    SET @ServiceID = '35'
    DECLARE @Name VARCHAR(8000) DECLARE @Desc VARCHAR(8000) DECLARE @Keywords VARCHAR(8000)
    SELECT @Name = [DefService].[Name],@Desc = [DefService].[Description],@Keywords = COALESCE(@Keywords + ', ', '') + [DefKeyword].[Name] FROM [DefService] INNER JOIN [DefKeywordServices] ON [DefService].serviceid = [DefKeywordServices].[ServiceID] INNER JOIN [DefKeyword] ON [DefKeyword].[KeywordID] = [DefKeywordServices].[KeywordID] WHERE [DefService].[ServiceID] = @ServiceID PRINT @Name + ' | ' + @Desc + ' | ' + @Keywords
    SELECT @Name AS Name,@Desc AS Description,@Keywords AS Keywords

    Results:
    Name Description Keywords
    GoToMyPC - Account Request Request a Citrix GoToMyPC account. GTMPC, GoTo, application, software, install, Installation, applications


    When I run the same query without a specific key it fails. The results only return a single row containing Name, Description and then ALL keywords for every key ID...very odd behavior.

    BTW, I need to do this in a single SQL query and not a stored proc or other method. I hope this is clear enough as any assistance is greatly appreciated! It's driving me CRAZY!
    Bruce

  2. #2
    Join Date
    May 2012
    Posts
    6
    Failing because you are trying to return multiple records to a single variable.

    Depending on what you want to do - personally I'd load it all into a temporary table or table variable.

    Then what do you want to do with the returned data? Just return it?

    SELECT id, name, description, keyword
    INTO #table_keywords
    FROM Service INNER JOIN keywords ON serviceid = serviceid

    SELECT * from #table_keywords

    DROP TABLE #table_keywords

    You'll need to sort out the field and table names above sorry.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm pretty sure that using a recursive assignment such as "@Keywords = COALESCE(@Keywords + ', ', '') + [DefKeyword].[Name]" won't work if it is not the only assignment in the statement. You may just have to run that string operation separately and, as SharkEnergy pointed out, it will need to be run on a single subset of the data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Why are variables used? And if you remove them?

    Code:
    SELECT 
        [DefService].[Name],
        [DefService].[Description],
        (COALESCE(@Keywords + ', ', '') + [DefKeyword].[Name]) as Keywords 
    FROM [DefService] 
    INNER JOIN [DefKeywordServices] ON [DefService].serviceid = [DefKeywordServices].[ServiceID] 
    INNER JOIN [DefKeyword] ON [DefKeyword].[KeywordID] = [DefKeywordServices].[KeywordID] 
    WHERE [DefService].[ServiceID] = @ServiceID
    Hope this helps.

  5. #5
    Join Date
    May 2012
    Posts
    6

    thanks for quick reply

    thanks, I realize this is why it's failing...my challenge is that I need to do this as a single select statement as opposed to using a temp table or stored proc...

    Basically, the name and description are single values while the key id will have multiple values for the keywords...is there a way to combine the keywords into a string and then add them to the column as my first example does?

    When I provide a single key it works fine, however not when I run against full table.

    This works perfectly...I need it to return the list against the whole table:

    DECLARE @ServiceID VARCHAR(10)
    SET @ServiceID = '35'
    DECLARE @Name VARCHAR(8000) DECLARE @Desc VARCHAR(8000) DECLARE @Keywords VARCHAR(8000)
    SELECT @Name = [DefService].[Name],@Desc = [DefService].[Description],@Keywords = COALESCE(@Keywords + ', ', '') + [DefKeyword].[Name] FROM [DefService] INNER JOIN [DefKeywordServices] ON [DefService].serviceid = [DefKeywordServices].[ServiceID] INNER JOIN [DefKeyword] ON [DefKeyword].[KeywordID] = [DefKeywordServices].[KeywordID] WHERE [DefService].[ServiceID] = @ServiceID PRINT @Name + ' | ' + @Desc + ' | ' + @Keywords
    SELECT @Name AS Name,@Desc AS Description,@Keywords AS Keywords

  6. #6
    Join Date
    May 2012
    Posts
    6

    to follow up

    I just need this query to run on all ServiceID values in the table as opposed to having to assign a single key value; as you can see this return the exact data I want, I just want it for all ServiceIDs:


    DECLARE @ServiceID VARCHAR(10)
    SET @ServiceID = '35'
    DECLARE @Name VARCHAR(8000) DECLARE @Desc VARCHAR(8000) DECLARE @Keywords VARCHAR(8000)
    SELECT @Name = [DefService].[Name],@Desc = [DefService].[Description],@Keywords = COALESCE(@Keywords + ', ', '') + [DefKeyword].[Name] FROM [DefService] INNER JOIN [DefKeywordServices] ON [DefService].serviceid = [DefKeywordServices].[ServiceID] INNER JOIN [DefKeyword] ON [DefKeyword].[KeywordID] = [DefKeywordServices].[KeywordID] WHERE [DefService].[ServiceID] = @ServiceID PRINT @Name + ' | ' + @Desc + ' | ' + @Keywords
    SELECT @Name AS Name,@Desc AS Description,@Keywords AS Keywords


    Results:
    Name Description Keywords
    GoToMyPC - Account Request Request a Citrix GoToMyPC account. GTMPC, GoTo, application, software, install, Installation, applications


    [Name Description Keywords
    (Name) GoToMyPC - Account Request
    (Description) Request a Citrix GoToMyPC account.
    (Keywords) GTMPC, GoTo, application, software, install, Installation, applications]
    Last edited by bluenova; 05-07-12 at 14:41.

  7. #7
    Join Date
    May 2012
    Posts
    6
    results:

    Name Description Keywords
    (Name) GoToMyPC - Account Request
    (Description) Request a Citrix GoToMyPC account.
    (Keywords) GTMPC, GoTo, application, software, install, Installation, applications

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The proper way to do this is to either use a user-defined function to return your concatenated string or, better, use a recursive Common Table Expression.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2012
    Posts
    213
    Try something like this:

    Code:
    SELECT 
        s.[Name],
        s.[Description],
        SUBSTRING( (SELECT ', ' + k.[Name]
                    FROM [DefKeywordServices] as ks 
                    INNER JOIN [DefKeyword] as k ON k.[KeywordID] = ks.[KeywordID] 
                    WHERE ks.[ServiceID] = s.[ServiceID] 
                    FOR XML PATH('') ), 3, 200000) as Keywords
    FROM [DefService] as s
    Hope this helps.

  10. #10
    Join Date
    May 2012
    Posts
    6

    Talking Awesome Imex!

    That worked perfectly, thank you so much...was banging my head so hard I got a concussion
    Much appreciated!

  11. #11
    Join Date
    May 2012
    Posts
    6

    Thumbs up Thanks to all, awesome forum

    BTW, just a huge thanks to all who helped here. This is a great forum with knowledgeable individuals giving super fast help! Thanks for letting me pick you brains!
    Bruce

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •