Mysql USING rename column

Question

What i'm trying to do is sub.blockid=id with the JOIN. I've renamed blockid as id in the subquery but the USING keyword is still attaching the column id instead of the renamed id column(blockid) from the subscribe table.

 $subscribequery = "JOIN (SELECT id as subid, blockid as id from subscribe WHERE userid='1') AS sub
    USING (id)";

This would be easy with ON but i have other JOIN subqueries with USING. I get the column is ambigious error if i try to use ON instead of USING along with the other subqueries.

Can you use both USING and ON in the same query? Can i rename the column as i have attempted above?

Here is the full query. The culprit is the second JOIN query.

SELECT * FROM products
JOIN (SELECT nid, sid, name AS max_name from products WHERE guideline='1' group by nid, sid) AS at1
        USING (nid, sid)
JOIN (SELECT id as subid, blockid as id from subscribe WHERE userid='1') AS sub
        USING (id)
WHERE id = subid AND name = max_name AND pending='0' AND deleted='0'
ORDER BY subid DESC, nid DESC;

Ike Walker · Accepted Answer · 2012-03-01 19:48:27Z

1

To clarify, you're not really renaming any columns, just aliasing them. And yes, since you are using a sub-query you can use the column alias in the ON or USING clause int eh outer query.

I recommend you use ON instead of USING, and simply specify which table or sub-query each column refers to using either the table name or the table alias.

Using ON and being explicit about which table you are referring to also makes your code more readable, especially considering that you are using multiple joins.

Here's an example using your query:

JOIN (SELECT nid, sid, name AS max_name from products WHERE guideline='1' group by nid, sid) AS at1
        ON at1.nid = products.nid and at1.sid = products.sid
JOIN (SELECT id as subid, blockid as id from subscribe WHERE userid='1') AS sub
        ON sub.id = products.id

answered Mar 1, 2012 at 19:48

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql USING rename column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related