At 03:46 16/09/2008, you wrote:
Message-ID: <[EMAIL PROTECTED]>
Date: Mon, 15 Sep 2008 18:33:04 +0100
From: "Stephen Wellington" <[EMAIL PROTECTED]>
To: "Mike Sullivan" <[EMAIL PROTECTED]>, 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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
