INNER JOIN - Two Tables, to unique fields. Won't work.

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 - Two Tables, to unique fields. Won't work.

Post by simonmlewis »

Code: Select all

$result = mysql_query ("SELECT catid, id, sceneid, scene FROM products INNER JOIN products ON products.id = products_scenes.prodid WHERE catid = '$c'") or die (mysql_error());
I'm being told:
[text]Not unique table/alias: 'products'[/text]
What am I doing wrong here?
I thought you select all the fields you want, then say from what Table, and then on what fields in each table you want to join them.

Even though I have done this the way the tutorial described, "sceneid" and "scene" are not in products, but it wasn't in the table in the tutorial either, only in the 'joining' table.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: INNER JOIN - Two Tables, to unique fields. Won't work.

Post by simonmlewis »

I've worked it out, as I knew I had done this before.

Code: Select all

$result = mysql_query ("SELECT products.catid, products.id, scene FROM products INNER JOIN products_scenes ON products.id = products_scenes.prodid WHERE catid = '$c' GROUP BY scene") or die (mysql_error());
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: INNER JOIN - Two Tables, to unique fields. Won't work.

Post by pickle »

If your problem's solved, please edit your post subject to include [solved].
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: INNER JOIN - Two Tables, to unique fields. Won't work.

Post by simonmlewis »

Actually, leading on from this, I have a similar INNER JOIN query that isn't working.

Code: Select all

$result = mysql_query ("SELECT subscribed_upload.email FROM subscribed_upload INNER JOIN unsubscribed ON subscribed_upload.email = unsubscribed.email WHERE email LIKE '%@%'") or die (mysql_error());
I have two tables. One is a fresh upload. The other is a list of emails that must NOT be included in an extract.

I need to extract all those from subscribed_upload WHERE email is NOT = unsubscribed.email.
I'm guess this has to be done in a JOIN... does it??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: INNER JOIN - Two Tables, to unique fields. Won't work.

Post by VladSun »

LEFT JOIN .... WHERE email IS NULL
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: INNER JOIN - Two Tables, to unique fields. Won't work.

Post by simonmlewis »

Code: Select all

$result = mysql_query ("SELECT subscribed_upload.email FROM subscribed_upload LEFT JOIN unsubscribed ON subscribed_upload.email = unsubscribed.email WHERE email IS NULL") or die (mysql_error());
Column 'email' in where clause is ambiguous
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: INNER JOIN - Two Tables, to unique fields. Won't work.

Post by simonmlewis »

Code: Select all

$result = mysql_query ("SELECT subscribed_upload.email FROM subscribed_upload LEFT JOIN unsubscribed ON subscribed_upload.email = unsubscribed.email WHERE unsubscribed.email IS NULL") or die (mysql_error());
?? Should this be it?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply