Message-ID: <D6.0A.58592.9F97EC84@xxxxxxxxxxxx>
To: php-db@xxxxxxxxxxxxx
Reply-To: "Mike Sullivan" <mjsulliv@xxxxxxx>
From: "Mike Sullivan" <mjsulliv@xxxxxxx>
Date: Mon, 15 Sep 2008 11:06:30 -0400
Subject: Building WHERE SQL clauses
Hello all. I'm using PHP to build a query for a database that consists of
multiple tables, all with identical attribues. A typical syntax try looks
like this: SELECT * FROM chico, harpo WHERE operator = "Bill" OR operator =
"Jessica"
MySQL responds with this: Couldn't execute query.Column 'operator' in where
clause is ambiguous
That's right. There's no way to distinguish between the column names
where they're identical between 2 tables, so often you would prefix
those with the table name such as (assuming you want to use harpo as
the canonical source) :
SELECT * FROM chico
LEFT JOIN harpo
ON `chico`.operator = `harpo`.operator
WHERE (
`harpo`.operator = "Bill" OR
`harpo`.operator = "Jessica"
);
However SQL also has a `USING` clause which can be used where columns
really are identical :
http://dev.mysql.com/doc/refman/5.0/en/join.html
"The USING(column_list) clause names a list of columns that must
exist in both tables"
So you should also be able to do
SELECT * FROM chico
LEFT JOIN harpo USING (operator)
WHERE (
`harpo`.operator = "Bill" OR
`harpo`.operator = "Jessica"
);
In that case you don't need to explicitly name the tables in the
USING() clause because the query parser notices that both tables have
columns named the same
HTH
Cheers - Neil
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
