This actually results in semantic problems in some queries, because Zend_Db_Select always assumes that a "Using" join is joining to the first table referenced in the query. This is not how the {{USING}} clause works in SQL.

$db->select()->from('A')->joinUsing('B', 'colname1')->joinUsing('C', 'colname2')

Should produce SQL syntax:

SELECT * FROM A JOIN B USING (colname1) JOIN C USING (colname2)

This should give the same results as this query:

SELECT * FROM A JOIN B ON B.colname1 = A.colname1 JOIN C ON C.colname2 = B.colname2

But in Zend_Db_Select, it produces the following SQL:

SELECT * FROM A JOIN B ON B.colname1 = A.colname1 JOIN C ON C.colname2 = A.colname2

Of course if table {{A}} has no {{colname2}}, this query returns an error.

If both {{A}} and {{B}} have a column named {{colname2}}, a query with {{USING (column2)}} is ambiguous, and should return an error. The SQL produced by Zend_Db_Select is not ambiguous, but it assumes that {{C}} is joined to {{A}}. This is in improper assumption.

Schema for above example:

CREATE TABLE A ( colname1 INT);
CREATE TABLE B ( colname1 INT, colname2 INT);
CREATE TABLE C ( coname2 INT);

SQL's {{USING}} clause actually knows which columns exist in which tables, and it decides which table you're trying to join based on which table has a column of the same name. So it knows that you intend to join {{C.colname2 = B.colname2}} because it knows {{B}} has a column {{colname2}}.

When multiple tables in the query contain a column of the same name, this is ambiguous and the semantics for {{USING}} can't guess what you intended. So it throws an error, which is the right thing to do.

Updating project management info: Minor, Should Have in Next Mini Release, assumed 2h of work.

I think that joinUsing should change to support USING like it should and at the same time it should support a joinOn. I spoke with Ralph about this issue and I came with a suggested implementation:

    public function joinOn($table, $onCondition) {
        if (strpos($onCondition, ' ') === false) {
            $onCondition = $this->_name . '.' . $this->_primary . ' = ' . $table . '.' . $onCondition;
        }

        $this->... = 'JOIN ' . $table . ' ON (' . $onCondition . ')';
    }

It's a pseudo-code... I'm not too focused on Zend Framework internals. So it's just an idea. Just a quick explanation:

The condition is there to support custom queries too. Examples:

$q->joinOn('B', 'column'); // JOIN B ON (A.column = B.column)

$q->joinOn('B', 'A.foo = B.column2'); // JOIN B ON (A.foo = B.column2)

After speaking with [~ralph] on the issue, it was decided to close this as "Won't Fix" for these two reasons:

• SQL Server and Sybase do not support USING clause
• JOIN USING and JOIN ON differ in the way they return columns. ie: JOIN ON would have t1.col and t2.col in the result set, but JOIN USING would have only col, so references to t1.col or t2.col in the WHERE clause would fail.

More information here: http://en.wikipedia.org/wiki/Join_%28SQL%29/…