-
Oct 3rd, 2018, 04:19 PM
#1
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.
-
Oct 4th, 2018, 01:49 AM
#2
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
-
Oct 4th, 2018, 10:30 AM
#3
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.
-
Oct 4th, 2018, 10:37 AM
#4
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
-
Oct 4th, 2018, 11:36 AM
#5
Re: Returning Values from FK
Probably you've found this link already
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...
-
Oct 4th, 2018, 01:53 PM
#6
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.
-
Oct 5th, 2018, 02:01 AM
#7
Re: Returning Values from FK
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|