mysql use result from select as where clause in UNION query

Question

I have table A whit this columns:

id   name      to_include
1    wanted1   null
2    wanted2   wanted1

If the query is for wanted2 then I need a way to use the value from to_include column (in this case wanted1) in the WHERE clause I have tried some like this:

SELECT * FROM (
    SELECT * FROM A WHERE name = 'wanted2'
) AS B
UNION
SELECT * FROM A WHERE A.name = B.to_include

That got this error: Unknown column 'B.to_include' in 'where clause

Expected result: A recordset with both two rows from the example table.

I think both is needed - UNION and JOIN. But the question isn't really clear. — Paul Spiegel
– Paul Spiegel, Commented Apr 29, 2017 at 20:57
Post what the result should look like, it's hard to tell from your description. I suspect @PaulSpiegel is right -- you need a JOIN in one query and then combine that with UNION. — Barmar
– Barmar, Commented Apr 29, 2017 at 20:58
Expected result: A recordset with both two rows from the example table. — MTK
– MTK, Commented Apr 29, 2017 at 20:59
Then you should be fine with Barmars answer. I was afraid, you would need to "include" the rows recursively. — Paul Spiegel
– Paul Spiegel, Commented Apr 29, 2017 at 21:07

Barmar · Accepted Answer · 2017-04-29 21:02:51Z

3

You can't refer to a previous query in a UNION as an alias. Each query in the UNION is processed independently, then the results are combined.

The second query in the UNION needs to be a JOIN, and you need to repeat the criteria from the first query when you do this.

SELECT * 
FROM yourTable WHERE name = 'wanted2'

UNION

SELECT b.*
FROM yourTable AS a
JOIN yourTable AS b ON b.name = a.to_include
WHERE a.name = 'wanted2'

DEMO

answered Apr 29, 2017 at 21:02

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

5 Comments

Paul Spiegel Over a year ago

Depending on data it's also possible to use only the second part with ON b.name IN (a.name, a.to_include)

Barmar Over a year ago

@PaulSpiegel Yeah, that works, but this code expresses the intent more clearly, IMHO.

MTK Over a year ago

@PaulSpiegel I have tested your version and yes that work also, but what you mean with "Depending on data"?

Paul Spiegel Over a year ago

@MTK If name is not UNIQUE you might get a different result (creating duplicates). Here's an exmple: sqlfiddle.com/#!9/02820/1

MTK Over a year ago

@PaulSpigel. Thank's. Is good for me to know that. I try to learn by little steps.

Collectives™ on Stack Overflow

mysql use result from select as where clause in UNION query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related