Using GROUP_CONCAT output for subquery

Question

I'm running into a problem, I have the following line in my query

CONCAT('',
      (SELECT GROUP_CONCAT(DISTINCT trips_loads_rel.load_id,'') AS x 
      FROM `trips_loads_rel` WHERE trips_loads_rel.trip_id = trips.Id)
) AS loads

It shows something like 8,10,27 (ie, numeric IDs), some Ids from trips_loads_rel table. It works ok. However, how can I use that output to pull matching records from other table? I mean, the line shows me all ids ok, but I need to query other table with these to pull related records. Actually I don't need these IDs, I need their matching records...

uvais · Accepted Answer · 2013-11-22 07:24:32Z

1

try this:

SELECT * FROM <other_table> WHERE <other_table>.load_id IN(CONCAT('',
     (SELECT GROUP_CONCAT(DISTINCT trips_loads_rel.load_id,'') AS x 
    FROM `trips_loads_rel` WHERE trips_loads_rel.trip_id = trips.Id)
) AS loads)

answered Nov 22, 2013 at 7:24

uvais

4162 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aram Alvarez Over a year ago

Ok! Almost there, thanks for the code. I did the following which half works: it shows the correct stuff once, then it repeats the same as many IDs in the CSV...: SELECT trips.Id, GROUP_CONCAT('', (SELECT loads.load_id FROM loads WHERE loads.Id IN (SELECT GROUP_CONCAT(trips_loads_rel.load_id,'') AS tmp FROM trips_loads_rel WHERE trips_loads_rel.trip_id = trips.Id))) AS loads FROM trips GROUP BY trips.Id

Aram Alvarez Over a year ago

Sorry...just tried, if I put DISTINCT in first GROUP_CONCAT it shows only one result. For instance "L131119-27", one of the load's IDs, where it should be like L131119-27, L131119-21, etc ie, as many loads IDs as matching :( without DISTINCT it just repeats "L131119-27" as it is the first match

uvais Over a year ago

ok try this one: DISTINCT(GROUP_CONCAT(trips_loads_rel.load_id))

Joachim Isaksson · Accepted Answer · 2013-11-22 05:53:02Z

0

It looks like it's a part of a query, not an entire one, so hard to show exact syntax, but if you want to use the values to find other rows, just don't group concat them to begin with.

Instead just use them directly doing something like;

SELECT * FROM `other_table` WHERE `other_table`.`load_id` IN
  (SELECT `load_id` FROM `trips_loads_rel` WHERE `trip_id` = `trips`.`Id`)

answered Nov 22, 2013 at 5:53

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

Comments

Ravinder Reddy · Accepted Answer · 2013-11-22 06:51:52Z

You can use FIND_IN_SET(col, 'csv as string') function to get the desired results.

Example:

mysql> select find_in_set( 2, '11,12,13,14,15,2' );
+--------------------------------------+
| find_in_set( 2, '11,12,13,14,15,2' ) |
+--------------------------------------+
|                                    6 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set( 2, '2,11,12,13,14,15,2' );
+----------------------------------------+
| find_in_set( 2, '2,11,12,13,14,15,2' ) |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> Select FIND_IN_SET( 6, '1,12,3,14,5,16,7,18,9,0,2,13,4,15,6,17,8' );
+--------------------------------------------------------------+
| FIND_IN_SET( 6, '1,12,3,14,5,16,7,18,9,0,2,13,4,15,6,17,8' ) |
+--------------------------------------------------------------+
|                                                           15 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

For your query you can pass the concatenated output '8,10,27' to compare with other_table's column.

Select find_in_set( other_table.col_name, '8,10,27' );

Refer To: MySQL String Functions: FIND_IN_SET()

Collectives™ on Stack Overflow

Using GROUP_CONCAT output for subquery

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related