PHP Developers Network
http://forums.devnetwork.net/

INNER JOIN help needed with two identical name fields
http://forums.devnetwork.net/viewtopic.php?f=2&t=142587
Page 1 of 1

Author:  simonmlewis [ Thu May 19, 2016 11:12 am ]
Post subject:  INNER JOIN help needed with two identical name fields

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.

Syntax: [ Download ] [ Hide ]
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'


So how do I do it? I think I got the "admin AS a" etc part wrong.

Author:  Celauran [ Thu May 19, 2016 12:25 pm ]
Post subject:  Re: INNER JOIN help needed with two identical name fields

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

Syntax: [ Download ] [ Hide ]
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'

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/