I need a SQL query

  • Thread starter rdx
  • Start date
  • #1
rdx
50
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.
 

Answers and Replies

  • #2
jim mcnamara
Mentor
4,230
2,799
Code:
SELECT name FROM foods WHERE type='fruit'
INTERSECT 
SELECT name FROM concepts WHERE type='color';
 
  • #3
verty
Homework Helper
2,164
198
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';
 
  • #4
rdx
50
0
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.
 

Related Threads on I need a SQL query

  • Last Post
Replies
3
Views
3K
  • Last Post
Replies
6
Views
710
Replies
4
Views
1K
Replies
1
Views
524
Replies
7
Views
1K
  • Last Post
Replies
4
Views
1K
  • Last Post
Replies
5
Views
710
  • Last Post
Replies
5
Views
832
  • Last Post
Replies
4
Views
2K
  • Last Post
Replies
2
Views
3K
Top