Page 1 of 1

INNER JOIN help needed with two identical name fields

Posted: Thu May 19, 2016 11:12 am
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.

Re: INNER JOIN help needed with two identical name fields

Posted: Thu May 19, 2016 12:25 pm
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'