Find Common Elements in Lists: SQL Query or PHP Nested Loops?

[rdx]

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]

SELECT name FROM foods WHERE type='fruit'
INTERSECT 
SELECT name FROM concepts WHERE type='color';

 

[verty]

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';

 

[rdx]

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.