[thelist] Mysql/PHP Searching WAS: Sql join problem

Alex Farran alex at alexfarran.com
Sat Dec 11 14:12:12 CST 2004


And here's another, better, solution.  This one has only one join and
allows you to make a wider range of queries.

To start with here's my data set:

select member.id, member.name, profile.category, profile.data
from member left join profile on profile.member_id = member.id;

+----+--------+----------+------------------+
| id | name   | category | data             |
+----+--------+----------+------------------+
|  1 | fred   | movie    | little mermaid   |
|  1 | fred   | movie    | fieval goes west |
|  1 | fred   | hobby    | hockey           |
|  2 | barney | movie    | little mermaid   |
|  2 | barney | movie    | fieval goes west |
|  2 | barney | hobby    | football         |
|  2 | barney | hobby    | skydiving        |
|  2 | barney | movie    | blade III        |
|  3 | wilma  | movie    | little mermaid   |
+----+--------+----------+------------------+

Now what I want from that is a table with columns for each of the
movies/hobbies I'm interested in.  I use the count and the group by
operators to count the number of times each member lists a particular
movie (this will be 0 or 1 since no member lists the same movie
twice).

Here I've chosen to count fieval goes west.

select member.id, member.name,
       count(if((profile.category='movie' and profile.data='fieval goes west'), 1, NULL)) as fieval
from member
left join profile on profile.member_id = member.id 
group by member.id;
       
+----+--------+--------+
| id | name   | fieval |
+----+--------+--------+
|  1 | fred   |      1 |
|  2 | barney |      1 |
|  3 | wilma  |      0 |
+----+--------+--------+

To only list members who like fieval goes west, I add a having clause.
Having is like where, except it operates on the results of the select
rather than the tables in the from clause.

select member.id, member.name,
       count(if((profile.category='movie' and profile.data='fieval goes west'), 1, NULL)) as fieval
from member
left join profile on profile.member_id = member.id 
group by member.id
having fieval=1;

+----+--------+--------+
| id | name   | fieval |
+----+--------+--------+
|  1 | fred   |      1 |
|  2 | barney |      1 |
+----+--------+--------+

You can use this structure to build any query you like.  Here I've
listed all the members who like the little mermaid and not fieval goes
west (wilma) or who like blade III (barney).

select member.id, member.name,
       count(if((profile.category='movie' and profile.data='fieval goes west'), 1, NULL)) as fieval,
	   count(if((profile.category='movie' and profile.data='little mermaid'), 1, NULL)) as mermaid,
	   count(if((profile.category='movie' and profile.data='blade III'), 1, NULL)) as bladeIII 
from member
left join profile on profile.member_id = member.id 
group by member.id 
having (fieval=0 and mermaid=1) or bladeIII=1;

+----+--------+--------+---------+----------+
| id | name   | fieval | mermaid | bladeIII |
+----+--------+--------+---------+----------+
|  2 | barney |      1 |       1 |        1 |
|  3 | wilma  |      0 |       1 |        0 |
+----+--------+--------+---------+----------+

Thanks to Rudy for pointing out that my first solution was a bit
cumbersome.

-- 

    __o    Alex Farran - Open source software specialist 
  _`\<,_   PHP | MySQL | E-Commerce | Content Management
 (_)/ (_)  www.alexfarran.com  
           phone:01273 474065  mobile:07790 389330


More information about the thelist mailing list