I've got the following 2 tables: create table cinema_order ( id int not null primary key auto_increment, revision_number int not null default 1, active_revision int not null default 1 ); create table cinema_order_revision ( id int not null primary key auto_increment, cinema_order_id int not null references cinema_order(id), cinema_order_revision_id int not null references cinema_order(id) );
I'm trying to JOIN the above two tables where the _revision table is merely a hanging table that couples an order with it's revision. so the following data: insert into cinema_order(revision_number, active_revision) values (1,0); insert into cinema_order(revision_number, active_revision) values (2,1); insert into cinema_order_revision(cinema_order_id, cinema_order_revision_id) values (1,2); this essentially pairs up cinema_order(id) 1 and cinema_order(id) 2 Why does the following query fail, or rather yield no results: SELECT r.cinema_order_revision_id FROM cinema_order_revision_tmp as r, cinema_order_tmp as o WHERE r.cinema_order_id = o.id and o.active_revision = 1 and r.cinema_order_id = 1; (I'm looking to find out what is paired up w/ a specific id, and get the active revision for said id) BUT, if I change the query to: ... and o.active_revision != 1 and .... then it works. Why is this? Thanks, Paul [EMAIL PROTECTED] _________________________________________________________________ Join the world’s largest e-mail service with MSN Hotmail. http://www.hotmail.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
