Date: 12/07/04 (MySQL Communtiy)    Keywords: no keywords

It's easier to show what I'm trying to do than explain it in english so here goes.

create table table1(
id int,
name varchar(15)
);

create table table2(
id int,
table1Id int,
variation varchar(15)
);

insert into table1 values(1, 'productA');
insert into table1 values(2, 'productB');

insert into table2 values(1, 1, 'gold');
insert into table2 values(2, 1, 'silver');
insert into table2 values(3, 1, 'platinum');
insert into table2 values(4, 2, 'gold');

Now, what I want is a query that will return all products but only one of the matching variations.
The following query returns all matching variations.

select name, variation from table1
inner join table2 on table2.table1Id = table1.id

RETURNS
productA, gold
productA, silver
productA, platinum
productB, gold

What I want is:
productA, gold --this can be any of the variations
productB, gold

Is there an easy way to do this?

Source: http://www.livejournal.com/community/mysql/43317.html