How to select data from columns in only one table when checking two tables limited by where clause in MySQL

Question

There are two tables. I need to select the columns from ONLY the table where the color Red is in the Color or ColorName column.

Columns
-------
moms: id,Name, Number, Color

dads: id,FullName, Phone, ColorName

Data
----
moms: 1, Jill, 832, Red

dads: 1, Jack, 123, Blue


-- SELECT * FROM moms,dads WHERE Color = Red or ColorName = 'Red' returns rows from both tables.  --

I only want all rows from moms table to be returned. The above returns all rows from both tables.

Seems like I need some type of reverse IN clause (i.e. where values are in column names).

DO I need a IF or IF Exists clause?

What are you asking to do? Why is `Select * from moms where color = red or colorname = 'red';` not correct? — Jay, Sep 05 '14 at 20:31
This is poorly designed. You should have a single table with both types of parents. — Kermit, Sep 05 '14 at 20:34
OP request `I need to select the columns from ONLY the table where the color Red is in the Color or ColorName column.` .When he said `I only want all rows from moms table to be returned. ` He is refering to data sample provided — Horaciux, Sep 05 '14 at 20:45
You should clarify your question - I believe you mix words `rows` and `columns` in the first sentence. It is not clear (at least to me) what you need. Your query does `CROSS JOIN` which lists all possible combinations from both tables. — Vojtěch Dohnal, Sep 05 '14 at 20:45
I don't understand what was so unclear about my question? I thought it was perfectly asked. Put the data in phpMyAdmin, and keep trying queries until you get the correct result like I did. @Kermit, not my design, I'm importing data into two different tables from two different companies. — Theodis Butler, Sep 05 '14 at 22:03

spencer7593 · Accepted Answer · 2014-09-06T00:50:50.047

Q: Do I need a IF or IF Exists clause?

A: No.

What you need to recognize is that your query is returning a semi-Cartesian product.

The syntax you have is equivalent to:

SELECT m.*
     , d.*
  FROM moms m
 CROSS
  JOIN dads d 
 WHERE m.Color = 'Red'
    OR d.ColorName = 'Red'

Basically, every row from moms is being matched to every row from dads. (Run the query without a WHERE clause, if you have 10 rows in moms and 10 rows in dads, you'll get back a total of 100 rows.

The WHERE clause is just filtering out the rows that don't meet the specified criteria.

Usually, when we use a SQL join operation, we include some predicates that specify which rows from one table are to match which rows from another table. (Typically, a foreign key matches a primary key.)

(I recommend you ditch the old-school comma syntax for the JOIN operation, and use the newer JOIN keyword instead.)

Firstly, what result set do you expect to be returned?

If you want rows from moms, you could get those with this query:

SELECT m.*
  FROM moms m
 WHERE m.Color = 'Red'

If you want rows from dads, then this:

SELECT d.*
  FROM dads d
 WHERE d.ColorName = 'Red'

If the columns in the moms and dads tables "line up", in terms of the number of columns, the order of the columns and the datatypes of the columns, you could use a UNION ALL set operator to combine the results from the two queries, although we would typically also include a discriminator column, to tell which query each row was returned from:

SELECT 'moms'      AS `source`
     , m.id        AS `id`
     , m.Name      AS `name`
     , m.Number    AS `number`
     , m.Color     AS `color`
  FROM moms m
 WHERE m.Color = 'Red'
 UNION ALL
SELECT 'dads'      AS `source`
     , d.id        AS `id`
     , d.FullName  AS `name`
     , d.Phone     AS `number`
     , d.ColorName AS `color`
  FROM dads d
 WHERE d.ColorName = 'Red'

Beyond those suggestions, it's hard to provide any more help, absent a clearer definition of the actual resultset you are attempting to return.

I would have loved to give you kudos for this answer but this is what I got when I copied and pasted the last set of SQL statements in phpMyAdmin: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE m.Color = 'Red' UNION ALL SELECT 'dads' AS `source` , d.id ' at line 6 — Theodis Butler, Sep 05 '14 at 22:09
DOH! The **`FROM`** clause is missing, in both those queries. My bad. I've edited the SQL text to include the `FROM moms m` And `FROM dads d` lines. (This is why we test.) — spencer7593, Sep 06 '14 at 00:49
I gave you the answer because it is the best way to do this with the best explanation. Thank you for correcting! — Theodis Butler, Sep 06 '14 at 06:09

jonasnas · Answer 2 · 2014-09-05T20:53:35.633

1

You can use Union:

select color as c from moms where color = 'Red'
union all
select colorName as c from dads where colorName = 'Red'

edited Sep 05 '14 at 20:53

answered Sep 05 '14 at 20:31

jonasnas

3,540
1
23
32

Please fix your spelling. – Kermit Sep 05 '14 at 20:33
1

I didnt down vote... but i'm not sure the union is the way to go. OP says he/she only wants mom records. – crthompson Sep 05 '14 at 20:40
Only from moms for that specific query case. Otherwise it doesn't make sense to use dads table at all. – jonasnas Sep 05 '14 at 20:42
Ya, really, the question doesnt make sense as it is... need more info. – crthompson Sep 05 '14 at 20:48
2

it makes sense. He's querying two tables (cross join) and gets combined columns from both tables. But he only needs red rows from any table but in his case 'Reds' are only in moms table. So union does what he needs. He wouldn't query dads at all in other case. P.S. fix 'where' clause – jonasnas Sep 05 '14 at 20:56
That does not return all rows. I upvote for good effort though. – Theodis Butler Sep 06 '14 at 00:18
:) and I thought 'use union' would be enough – jonasnas Sep 06 '14 at 00:38

Horaciux · Answer 3 · 2014-09-05T20:37:36.570

1

    SELECT * FROM moms  WHERE Color = 'Red'
   UNION ALL
    SELECT * FROM  dads WHERE   ColorName = 'Red'

edited Sep 05 '14 at 20:37

answered Sep 05 '14 at 20:32

Horaciux

6,322
2
22
41

you are a genius! thank you for reading the question. probably even testing. and providing the solution! Literally banged by head against the wall on this for 4 hours. – Theodis Butler Sep 05 '14 at 22:01
@TheodisButler I feel disappointed this morning when I see negative reputation. Look at this: http://stackoverflow.com/tour "Accepting doesn't mean it's the best answer, it just means that it worked for the person who asked." and I add `in time` but this is just my opinion, timing is critical in my job. Best regards – Horaciux Sep 06 '14 at 11:49

How to select data from columns in only one table when checking two tables limited by where clause in MySQL

3 Answers3