CODEWITHSUNDEEP
mysqlsqlunionmysql-5.5
Josh
Josh

Reputation: 7415

In MySQL, can I SELECT with UNION but limit the rows from the second query based on the first query?

I have two tables that I need to combine into one query (bad software), from tables with different schema. I can fake one table easily to take care of the schema differences but the kicker is that I only want a subset of rows from the second table based on (joined with) info from the first table. My last resort is making a stored proc and temporary table, but I am wondering if it's possible to do this with a query. This is in MySQL 5.5

Say I have something like:

Cats           BadCats
----           -------
id             cat_id
name           color
weight         badness
length
color
awesomeness

I want to select all blue cats and bad cat values for cats that are in the blue result set. Like:

SELECT id,name,awesomeness from Cats where color = 'blue'
UNION
SELECT cat_id,null,badness from BadCats
    where BadCats.cat_id = Cats.id
    and BadCats.color = 'blue' -- or BadCats.color = Cats.color

Another contrived example of what I'd like to do would be:

SELECT id,name from Cats c JOIN BadCats bc on bc.cat_id=c.id

But fake BadCat's columns somehow, so the join would work. My stored proc would look something like:

CREATE TABLE temp
SELECT id,name,awesomeness from Cats where color = 'blue' INTO temp
SELECT cat_id,null,badness from BadCats where cat_id in (select distinct id from Cats) INTO temp
return * from temp

Or potentially:

CREATE TABLE temp
SELECT cat_id,null,badness from BadCats INTO temp -- same schema
SELECT id,name,awesomeness from Cats LEFT JOIN BadCats on id=cat_id
    where Cats.color = 'blue'

Or even more potentially, a view on BadCats with the same schema as Cats...

The end goal here is to get a list of blue cats and an idea of how awesome/bad they are by including rows in BadCats matching the Cats that were found, so the software can process the rows based on id.

Upvotes: 0

Views: 460

Answers (3)

Sin
Sin

Reputation: 1864

SELECT id,name,awesomeness from Cats where color = 'blue'

UNION

SELECT cat_id,null,badness from BadCats
    where cat_id in
    ( SELECT id from Cats where color = 'blue')
    and color = 'blue

Upvotes: 1

Jhecht
Jhecht

Reputation: 4435

From what I just read I think you should be able to do something akin to:

SELECT c.* FROM Cats as c WHERE c.color="blue"
UNION
SELECT bc.* FROM BadCats as bc WHERE bc.cat_id = c.id

Although Here is a W3Schools Article On Left Join which may be more of what you are trying to get. Similarly, there is a RIGHT Join, and Also INNER Join.

I believe, unless I totally misunderstand what you are trying to do, you will want to do something more like

SELECT c.*,bc.* FROM Cats as c, BadCats as bc WHERE c.color="blue" and bc.cat_id = c.id

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562731

This doesn't need to be a UNION, this is simply an outer join.

SELECT id, name from Cats c 
LEFT OUTER JOIN BadCats bc on bc.cat_id=c.id
WHERE c.color = 'blue'

You get all the blue cats from Cats, whether there is a matching bc.cat_id or not.

Then if there is a matching row in BadCats with the same id, it is joined.
If there is not a matching bad cat for a given cat, all the columns from BadCats are "faked" with NULLs.

See http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ for more on join types. It would be worth your time to learn how to use them.

Upvotes: 1

Related Questions