Message-ID: <D6.0A.58592.9F97EC84@pb1.pair.com>
To: php-db@lists.php.net
Reply-To: "Mike Sullivan" <mjsulliv@uvm.edu>
From: "Mike Sullivan" <mjsulliv@uvm.edu>
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