dcsimg
Results 1 to 7 of 7

Thread: Returning Values from FK

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,479

    Returning Values from FK

    The primary table that I'm pulling data from is named auto_responder and it has the following (and this is actual data):
    Code:
    +-------------------+---------+-------------+-------------+--------------+
    | auto_responder_id | user_id | customer_id | sequence_id | date_entered |
    +-------------------+---------+-------------+-------------+--------------+
    |        3          |    2    |      1      |    null     |  2018-10-03  |
    |        4          |    4    |      2      |    null     |  2018-10-03  |
    |        5          |    2    |      3      |      8      |  2018-10-03  |
    +-------------------+---------+-------------+-------------+--------------+
    I am running a query to return the name of the user that entered the row, the name of the customer that the row represents, the customer's id, the date the row was added, the sequence's title (if applicable, otherwise let them know the row was added via an alternative means), and then all of the queue_id values from a separate table where the customer_id from the auto_responder table match respectively, but only return rows for the users that share the same business_id value (user's 2 & 4 both share the same business_id). I have everything working except for getting the queue_id values.

    This is the query that works to pull everything but the queue_id values: code1.txt

    However, whenever I try to get the queue_id values weird things happen. So for example, if I try this: Attachment 162233

    Then only the first row is returned. And if I try to use a subquery instead of a JOIN statement, like this: Attachment 162231

    I get the error:
    #1242 - Subquery returns more than 1 row
    Which is kind of the point, I want it to return all of the values and concatenate them.

    P.S. - Had to put some of my code in text files as attachments because of the "Oops something went wrong" issue the forum's been having.

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,982

    Re: Returning Values from FK

    Your attachments are coming back as invalid for me but I think I can understand the problem from your description.

    Check out Shaggy's thread here. He was facing the exact same problem. If you're using 2017 then the String_Agg function that Zvoni referenced is your friend. Otherwise, check out the stack overflow link I posted where they describe how to use the FOR XML operation to achieve it.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,479

    Re: Returning Values from FK

    The MySQL that I'm using must not be new enough because I get the following error:
    #1305 - FUNCTION *.STRING_AGG does not exist
    I'll take a look at the other method.
    Last edited by dday9; Oct 4th, 2018 at 11:33 AM.

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,504

    Re: Returning Values from FK

    Yeah, not new enough, or the fact that I was using SQL Server, rather than MySQL.
    My usual boring signature: Nothing

  5. #5
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,303

    Re: Returning Values from FK

    Probably you've found this link already

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  6. #6

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,479

    Re: Returning Values from FK

    So, I was ultimately able to get everything to work and since I was building the HTML from the query I decided to return the HTML inside the query rather than building it once it was received. But here was the final SQL query works as needed: working code.txt (man the forum does not like complex SQL queries).

    So basically I still used the GROUP_CONCAT function, but I also added the last LEFT JOIN with some conditions.

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,982

    Re: Returning Values from FK

    The MySQL that I'm using
    Ah, sorry. I just assumed you were using SQL Server. Took a look at the Group concat function, though, and it looks like exactly what you're after (basically the MySQL equivalent of SQLServer's String_Agg).
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width