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

  • Thread starter Thread starter rdx
  • Start date Start date
  • Tags Tags
    Sql
Join the discussion
Ask a follow-up here, or get your own question answered by working scientists, mathematicians and engineers — people, not an autocomplete.
Real named experts · corrections over time · the nuance an AI answer skips
Sign up Log in
3 replies · 4K views
rdx
Messages
50
Reaction score
0
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.
 
on Phys.org
Code: 
SELECT name FROM foods WHERE type='fruit'
INTERSECT 
SELECT name FROM concepts WHERE type='color';
 
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';
 
verty said:
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.