# I need a SQL query

I get a list from a query. Let's say "SELECT name FROM foods WHERE type='fruit';" returns "apple, orange, banana"

I get a list from another query, "SELECT name FROM concepts WHERE type='color';" returns "red, orange, yellow"

Now what I want is to find the common element(s) in the two lists, in this case "orange."

What is the most efficient way to do this? SQL queries or PHP nested loops, or ???

Thanks.

jim mcnamara
Mentor
Code:
SELECT name FROM foods WHERE type='fruit'
INTERSECT
SELECT name FROM concepts WHERE type='color';

verty
Homework Helper
I would have thought of using a join:

select f.name from foods f inner join concepts c on f.type = 'fruit' and f.name = c.name and c.type = 'color';

I would have thought of using a join:

It works well with MySQL. It will be a while before I have comparative stats but this certainly works easily. Thanks.