Returning Values from FK

[dday9]

The primary table that I'm pulling data from is named auto_responder and it has the following (and this is actual data):

+-------------------+---------+-------------+-------------+--------------+
| 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.

[FunkyDexter]

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.

[dday9]

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.

[Shaggy Hiker]

Yeah, not new enough, or the fact that I was using SQL Server, rather than MySQL.

[Ecniv]

Probably you've found this link already

[dday9]

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.

[FunkyDexter]

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).