MySQL WHERE IN subquery with multiple columns

Question

I'm creating a SQL query / subquery that will return a list of "class days". The query I'm using seems to not be recognizing the values from the subquery, and I cannot figure out why. If I run the subquery, I get the correct values. For some reason when I try to run the entire query & subquery together it doesn't work correctly, and I only get results for the 1st value in the comma separated list. If I run the query below, I only get results for tblclasses.cla_ID = 1234:

SELECT 
    *
FROM
    classdays
WHERE
    classdays.classday_classid IN (SELECT 
            CONCAT_WS(', ',
                        tclasses.cla_ID,
                        tclasses.cla_nextclass,
                        tclasses_1.cla_nextclass) AS nextclasses
        FROM
            tclasses
                LEFT JOIN
            tclasses AS tclasses_1 ON tclasses.cla_nextclass = tclasses_1.cla_ID
        WHERE
            tclasses.cla_ID = 1234)

However if I run just the subquery, it works and I get the list of values: 1234, 5678, 9012

(SELECT CONCAT_WS(', ',tclasses.cla_ID,tclasses.cla_nextclass,tclasses_1.cla_nextclass) AS nextclasses
   FROM
       tclasses
   LEFT JOIN
       tclasses AS tclasses_1 ON tclasses.cla_nextclass = tclasses_1.cla_ID
   WHERE
       tclasses.cla_ID = 1234)

And if I simply run the query with the values in the IN statement like so, it works as well:

SELECT 
    *
FROM
    classdays
WHERE
    classdays.classday_classid IN (1234, 5678, 9012)

Any help would be greatly appreciated, as for some reason I can't get the IN statement to run on those 3 comma separated values

Thanks!

IN doesn't work with comma-delimited strings. When you use x IN (subquery) it does an exact comparison to each row of the subquery result. — Barmar
– Barmar, Commented Jun 18, 2020 at 18:11
Instead of combining the columns with CONCAT_WS, put them in separate rows using UNION. — Barmar
– Barmar, Commented Jun 18, 2020 at 18:13
The 3 comma separated values are treated as a literal string 'value1, value2, value3'. — Eric
– Eric, Commented Jun 18, 2020 at 18:21
@Barmar - I've never really used a UNION query before. I understand the basics of it, but since I'm essentially joining the same table on itself LEFT JOIN tclasses AS tclasses_1 ON tclasses.cla_nextclass = tclasses_1.cla_ID how would that UNION query look? — dzbow_PL10
– dzbow_PL10, Commented Jun 18, 2020 at 18:48

Paul Spiegel · Accepted Answer · 2020-06-18 19:28:55Z

You can JOIN classdays with your subquery but without CONCAT_WS, and use an IN condition in the ON clause:

SELECT classdays.*
FROM classdays
JOIN (
  SELECT 
    tclasses.cla_ID,
    tclasses.cla_nextclass as next1,
    tclasses_1.cla_nextclass as next2
  FROM tclasses
  LEFT JOIN tclasses AS tclasses_1
    ON tclasses.cla_nextclass = tclasses_1.cla_ID
  WHERE tclasses.cla_ID = 1234
) c ON classdays.classday_classid IN (c.cla_ID, c.next1, c.next2)

You might need SELECT DISTINCT classdays.*, if the subquery can return more than one row, or wehen the list c.cla_ID, c.next1, c.next2 can contain duplicates.

What Barmar mentioned with UNION would be

SELECT classdays.*
FROM classdays
WHERE classdays.classday_classid IN (
  SELECT tclasses.cla_ID
  FROM tclasses 
  WHERE tclasses.cla_ID = 1234

  UNION

  SELECT tclasses.cla_nextclass
  FROM tclasses
  WHERE tclasses.cla_ID = 1234

  UNION 

  SELECT tclasses_1.cla_nextclass
  FROM tclasses
  JOIN tclasses AS tclasses_1 ON tclasses.cla_nextclass = tclasses_1.cla_ID
  WHERE tclasses.cla_ID = 1234
)

Note that you don't need a JOIN for the first two UNION parts. And for last part you can use INNER JOIN instead of LEFT JOIN.

tpdi · Accepted Answer · 2020-06-18 18:15:51Z

0

You can't dynamically make an IN list from a string.

But you also don't need to, you can just join classdays.classday_classid to the set returned by the subquery.

answered Jun 18, 2020 at 18:15

tpdi

35.3k11 gold badges83 silver badges123 bronze badges

1 Comment

dzbow_PL10 Over a year ago

Ok that makes sense about not being able to dynamically make an IN list from a string. Regarding joining in classdays.classday_classid to the set returned by the subquery, how do I do this since it's 3 separate columns returned, rather than 3 rows? I'm a beginner to SQL and stumped on this! The help is much appreciated

Collectives™ on Stack Overflow

MySQL WHERE IN subquery with multiple columns

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related