CODEWITHSUNDEEP
mysql
Crazy Serb
Crazy Serb

Reputation: 76320

Sub-query using values from parent query with IN (...) query?

Ok, I'm a little lost... not sure if this is just not possible or if I'm just missing something...

I've got a column called "products" in "invoices" table that has a comma-separated list of product IDs (such as 1,2,3), and another table called "products" whose SKUs I am trying to fetch in a single query and have available as a comma-separated list as well, so if the original "products" column contains "1,2,3" as an entry I want to have available another "skus" column in the results that has entries such "DD093,RT883,KR933" (sample SKUs matching those product IDs).

Here is what I have that works just fine (passing a specific list of product ids):

SELECT i.*, (SELECT GROUP_CONCAT(sku) FROM products p WHERE p.id IN (1,2,3)) AS skus 
FROM invoices i
WHERE 1 
ORDER BY i.id DESC

and that returns something like this:

id | products | skus
1 | 1,2,3 | DD093,RT883,KR933

But if I pass something like this:

SELECT i.*, (SELECT GROUP_CONCAT(sku) FROM products p WHERE p.id IN (i.products)) AS skus 
FROM invoices i
WHERE 1 
ORDER BY i.id DESC

the query breaks...

so how to get the values from i.products in a sub-query like that?

And is that even possible?

P.S. - Before you even suggest I normalize the data, or judge me for using an awful setup like that, keep in mind that it's done on purpose because creating separate records for the "invoice" table for each product is going to complicate a lot more stuff on the front-end and I don't want nor do I need to go there. This is just a log of sorts and would make the front-end display nicer for that one column, and that's why I'm trying to get it done within a single query and asking if it's possible - if it is, how? If not, I'd like a definite answer on that too.

Upvotes: 0

Views: 115

Answers (1)

rlanvin
rlanvin

Reputation: 6277

And is that even possible?

Maybe, hard to say for sure without testing. You could try something like:

SELECT i.*, (
  SELECT GROUP_CONCAT(sku) FROM products p
  WHERE FIND_IN_SET(p.id, i.products) > 0
) AS skus 
FROM invoices i
WHERE 1 
ORDER BY i.id DESC

Upvotes: 1

Related Questions