Re: Re: Building WHERE SQL clauses

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



At 03:46 16/09/2008, you wrote:
Message-ID: <29c0d3a50809151033o45c17c0aj7af050ae4498662@xxxxxxxxxxxxxx>
Date: Mon, 15 Sep 2008 18:33:04 +0100
From: "Stephen Wellington" <wellingtonsteve@xxxxxxxxx>
To: "Mike Sullivan" <mjsulliv@xxxxxxx>, php-db@xxxxxxxxxxxxx
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Subject: Re:  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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux