Re: Re: [PHP-DB] Building WHERE SQL clauses

At 03:46 16/09/2008, you wrote:

Message-ID: <29c0d3a50809151033o45c17c0aj7af050ae4498662@mail.gmail.com> Date: Mon, 15 Sep 2008 18:33:04 +0100 From: "Stephen Wellington" <wellingtonsteve@gmail.com> To: "Mike Sullivan" <mjsulliv@uvm.edu>, php-db@lists.php.net MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Subject: Re: [PHP-DB] Building WHERE SQL clauses You probably want something like this: SELECT * FROM chico as c, harpo as h WHERE c.operator = "Bill" OR c.operator = "Jessica" OR h.operator = "Bill" OR h.operator ="Jessica"

With all due respect, I think that's going to be a cause of major pain. You should try it out with the example table structures below. I've added a PK which doesn't ovelap, so you can see the result columns from both tables numerically It's going to end up doing a FULL JOIN on each row from chico which matches one of the 2 conditions, against the rows in harpo, which is definitely the wrong result here. I'm assuming the OP wants at most one row from either table which contains the other values from the table matching the result filter. On later consideration (he didn't make clear what the desired result was) it could well be the UNION result he's after, if both tables really are identical in structure, as mentioned by the later message (unless that was yours ;-) CREATE TABLE chico ( item smallint(6) NOT NULL AUTO_INCREMENT, operator char(32) NOT NULL, PRIMARY KEY (item) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; -- Duplicate chico table structure exactly CREATE TABLE harpo LIKE chico; INSERT INTO chico (item, operator) VALUES (1, 'Bill'), (2, 'Jessica'), (3, 'Dave'), (4, 'Clara'), (5, 'Ally'), (6, 'Josh'), (9, 'Mark'), (10, 'Sophie'); INSERT INTO harpo (item, operator) VALUES (13, 'Mark'), (14, 'Sophie'), (15, 'Bill'), (16, 'Jessica'); HTH Cheers - Neil