On 06/21/2004 04:26 PM [EMAIL PROTECTED] spoke:
I don't understand. You want every other record except .....what?
By flipping the equality the way you did, you should see all of the
records where a.id = b.id (regardless of what the b.type value is) where
a.type was not 'X' which is one reasonably valid solution to the statement "all
of the rows NOT found by the above query"
The more details you give us the better we can help you. If you can
give us your SHOW CREATE TABLE statements and possibly some sample data, we
should be able to get at exactly the data you want to see.
Respectfully,
Shawn Green
drop table a;
create table a (
id int auto_increment,
name varchar(20),
primary key (id)
);
drop table b;
create table b (
id int auto_increment,
a_id int not null,
name char(20),
type char,
primary key (id)
);
insert into a (name) values ("row1");
insert into b (a_id, name, type) values(1, "1b", "X");
insert into b (a_id, name, type) values(1, "2b", "Y");
insert into b (a_id, name, type) values(1, "3b", "Y");
insert into b (a_id, name, type) values(1, "4b", "Z");
insert into a (name) values ("row2");
insert into b (a_id, name, type) values(2, "1a", "X");
insert into b (a_id, name, type) values(2, "2a", "Y");
insert into b (a_id, name, type) values(2, "3a", "Y");
insert into b (a_id, name, type) values(2, "4a", "Z");
insert into a (name) values ("row3");
insert into b (a_id, name, type) values(3, "2a", "Y");
insert into b (a_id, name, type) values(3, "3a", "Y");
insert into b (a_id, name, type) values(3, "4a", "Z");
select a.name from a, b where b.type='X' and a.id=b.a_id;
returns row1 and row1 from a.
Now I need a select that will return row3 but not rows 1 or 2.