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

Alex Farran alex at alexfarran.com
Sat Dec 11 06:24:13 CST 2004


Hi Codi,

Here's my solution to your problem.

First, from your description I think your tables must look something
like this:

create table member (
	id int not null auto_increment primary key,
	name  varchar(50),
	sex   enum('male','female'),
	email varchar(50),
	country varchar(50));

create table profile (
	id int not null auto_increment primary key,
	member_id int not null,
	category enum('movie', 'hobby'),
	data varchar(100));

Here's a very small set of test data:

insert into member (name, sex, email, country) 
values ('fred','male','fred at flintstone.com','us');

insert into profile (member_id, category, data)
values 
(1,'movie','little mermaid'),
(1,'movie','fieval goes west'),
(1,'hobby','hockey');

Using left join you can create, for the duration of the query, a
composite table that you may query exactly as you would a single
table.  For example:

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           |
+----+------+----------+------------------+

The two tables are joined by the condition profile.member_id =
member.id.  All the data from the left table is returned at least
once.  If there is no data matching the condition in the right table
then its fields are null.

For example, if I create a new member with no profile information:

insert into member (name, sex, email, country) 
values ('barney','male','barney at rubble.com','us');

and run the same query again. I get:

+----+--------+----------+------------------+
| id | name   | category | data             |
+----+--------+----------+------------------+
|  1 | fred   | movie    | little mermaid   |
|  1 | fred   | movie    | fieval goes west |
|  1 | fred   | hobby    | hockey           |
|  2 | barney | NULL     | NULL             |
+----+--------+----------+------------------+

You can see how you might query this table to find members who liked
one hobby or one movie, but you can't query for combinations of
hobbies and movies.  To do that you must join the member table with
the profile table multiple times.  Say for instance you wanted to know
all the members who liked little mermaid, fieval goes west and played
hockey.  First you'd join the member table to the profile table three
times:

select  member.id, member.name, MOVIE1.data as movie1, MOVIE2.data as movie2, HOBBY1.data as hobby1 from member
left join profile as MOVIE1 on MOVIE1.member_id = member.id 
left join profile as MOVIE2 on MOVIE2.member_id = member.id 
left join profile as HOBBY1 on HOBBY1.member_id = member.id 
where MOVIE1.category = 'movie' 
and MOVIE2.category = 'movie'
and HOBBY1.category = 'hobby';

I've used table aliases so that in the join condition I can refer
to each of the three instances of the profile table.  The where clause
filters the results so that each profile column contains only data
in a particular category.

+----+--------+------------------+------------------+--------+
| id | name   | movie1           | movie2           | hobby1 |
+----+--------+------------------+------------------+--------+
|  1 | fred   | little mermaid   | little mermaid   | hockey |
|  1 | fred   | little mermaid   | fieval goes west | hockey |
|  1 | fred   | fieval goes west | little mermaid   | hockey |
|  1 | fred   | fieval goes west | fieval goes west | hockey |
+----+--------+------------------+------------------+--------+

This table can be queried for any two movies and any hobby.  So your
query now looks like this:

select member.id, member.name, MOVIE1.data as movie1, MOVIE2.data as movie2, HOBBY1.data as hobby1 from member
left join profile as MOVIE1 on MOVIE1.member_id = member.id 
left join profile as MOVIE2 on MOVIE2.member_id = member.id 
left join profile as HOBBY1 on HOBBY1.member_id = member.id 
where MOVIE1.category = 'movie' 
and MOVIE2.category = 'movie'
and HOBBY1.category = 'hobby'
and MOVIE1.data = 'little mermaid'
and MOVIE2.data = 'fieval goes west'
and HOBBY1.data = 'hockey';

Which returns:

+----+------+----------------+------------------+--------+
| id | name | movie1         | movie2           | hobby1 |
+----+------+----------------+------------------+--------+
|  1 | fred | little mermaid | fieval goes west | hockey |
+----+------+----------------+------------------+--------+

-- 

    __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