Extract column from subquery

[silkfire]

Dunno if I'll get much help here but thought I'd give it a try. I'm running a subquery which returns 2 columns, one with substance and one with status. I then want to run a query on this data set to ONLY get the substance (I need the status as a helper column but need to get rid of it to run an IN query).

 

SELECT set.substance FROM (SELECT substance, (SELECT  parameters.state FROM parameters
				      WHERE products.vnr = parameters.vnr
				      AND parameters.plnt = 'SE20'
                                              AND  parameters.state <> 'passive') as state
                                              FROM `products` WHERE products.substance IN (SELECT substance_mapping.substance FROM substance_mapping WHERE dead <> 'yes')
                                              AND company LIKE '%company%') as set
WHERE set.state <> NULL

 

And get error Subquery returns more than 1 row. It is supposed to return multiple rows, why is MySQL complaining? I have aliases, so what's wrong with the query?

[sunfighter]

Dunno if I'll get much help here but thought I'd give it a try.

Yes, one of the best ways to get help is to insult everyone before asking your question.

 

Here's a question for you:

 

If you have the information from your first query already why not use that instead of doing another query?

[silkfire]

Well I didn't get any sufficient answers last time. I wanted to run a query on a subquery. How do you run an IN (...) on something that returns 2 columns, aye?

[sunfighter]

Sorry silkfire know nothing of past sins.  :'(

 

But in this case - you have a query that gives two columns; (right?) one you want; one you don't. So $results give an array with two elements. $array[0] and $array[1]. Why can't you just use one of those for your needs??