MySQL Multiple Where Clauses Using One Table

Question

I have a table like this:

user_id, name, second_name

Sample data:

1, "fred", "smith"
2, "john", "smith"
3, "karl", "johnson"

I want to find all the people that have a name of say "fred". Then I want to find all the people that have the same second name as "Fred". I would like to do this in one query if possible. A query simialar to this:

SELECT * FROM people WHERE name=:first_name OR second_name=:result_from_first_row

The output of this sample query would be:

1, "fred", "smith"
2, "john", "smith"

It needs to be optional and secondly I don't know what the 'second name' is until the query gets Fred. — Nuvolari
– Nuvolari, Commented Feb 17, 2013 at 18:08
I misread, sorry. Then I don't know the answer, hope someone does. What do you expect to get if there's two or more "fred"? — Francisco Presencia
– Francisco Presencia, Commented Feb 17, 2013 at 18:09

Felix Ebert · Accepted Answer · 2013-02-17 20:06:28Z

1

SELECT * FROM people WHERE second_name IN (SELECT second_name FROM people WHERE name=:first_name)

edited Feb 17, 2013 at 20:06

answered Feb 17, 2013 at 18:19

Felix Ebert

1,18111 silver badges32 bronze badges

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

3 Comments

Nuvolari Over a year ago

That gave me an error but this seemed to work: SELECT * FROM people WHERE second_name=(SELECT second_name FROM people WHERE name='fred')

Nuvolari Over a year ago

However I don't believe that's optional.

Felix Ebert Over a year ago

please try it again, i used "IS IN" instead of a simple "IN" accidentally

Collectives™ on Stack Overflow

MySQL Multiple Where Clauses Using One Table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related