INNER JOIN help needed with two identical name fields

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

INNER JOIN help needed with two identical name fields

Post by simonmlewis »

We are building this system for users to add products, but we have added a means to block the user.

Now we need to look for all products for a given category name, but NOT if the user is blocked.

Trouble is, the code here is using a.id and p.id and it reckons they are not unique.

[text]SQL query: Documentation

SELECT a.id, p.id, title, catid, p.catname, price, userid, status FROM products AS p, admin AS a INNER JOIN products AS p on p.userid = a.id WHERE p.catname =:category AND a.status <> 'block'

MySQL said: Documentation
#1066 - Not unique table/alias: 'p' [/text]

So how do I do it? I think I got the "admin AS a" etc part wrong.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: INNER JOIN help needed with two identical name fields

Post by Celauran »

Specify your products table in the FROM or the JOIN, but not both.

Code: Select all

SELECT a.id, p.id, title, catid, p.catname, price, userid, status
FROM admin AS a
INNER JOIN products AS p on p.userid = a.id
WHERE p.catname =:category AND a.status <> 'block'
Post Reply