Page 1 of 1

Help with Joins

Posted: Fri Apr 21, 2006 3:04 pm
by BigAbe
Aloha,

I have 4 tables [users, ads, subcategories, and categories].

Each record in the ads table has:
  • A userID number associated with a userUID in the users table
    A categoryID associated with a categoryID in the categories table
    A subcategoryID associated with a subcategoryID in the subcategories table
Both the category table and the subcategory table each have both an ID column and a Name column.


I'm trying to display all ads with their respective category and subcategory names, but when I try this code, no records are returned:

Code: Select all

SELECT `userUID`, `adID`, `adTitle`, 
 `adCategoryID`, `adSubcategoryID`, `catName`, `subcatName` 
FROM users, ads, categories, subcategories
WHERE (users.userID = ads.adUser) 
AND (ads.adCategoryID = categories.catID)
AND (ads.adSubcategoryID = subcategories.subcatID)
LIMIT 10
My knowledge of mySQL joins is somewhat limited, so any help anyone can offer would be greatly appreciated.

Mahalo!

Posted: Sat Apr 22, 2006 6:17 pm
by BigAbe
Anyone?

Re: Help with Joins

Posted: Sat Apr 22, 2006 7:05 pm
by visionmaster
Hello,

Try out this:

Code: Select all

SELECT users.userUID AS userid, ads.adID AS adid, ads.adTitle AS adtitle, categories.adCategoryID AS catid, subcategories.adSubcategoryID AS subcatid, categories.catName AS catname, subcategories.subcatName AS subcatname 
FROM ads
LEFT JOIN ads ON (ads.adCategoryID = categories.catID  AND ads.adSubcategoryID = subcategories.subcatID)
WHERE users.userID = ads.adUser
Regards,
visionmaster

Posted: Sat Apr 22, 2006 7:11 pm
by shoebappa
You need to use the table.fieldname format for your select. http://builder.com.com/5100-6388-1050307.html

Code: Select all

SELECT ads.userUID, ads.adID,  ads.adTitle, ads.adCategoryID, ads.adSubcategoryID, categories.catName, subcategories.subcatName 
FROM users, ads, categories, subcategories 
WHERE (ads.adUser = users.userID) 
AND (ads.adCategoryID = categories.catID) 
AND (ads.adSubcategoryID = subcategories.subcatID) 
LIMIT 10
Guessing what fields are in what tables there but hopefully that would work. I also swapped ads.adUser = users.userID just in case, not sure it has to be. If not here's what access came up with...

Code: Select all

SELECT ads.user_id, ads.ad_id, ads.title, ads.cat_id, ads.subcat_id, categories.name, subcategories.name
FROM users INNER JOIN (subcategories INNER JOIN (categories INNER JOIN ads ON categories.cat_id = ads.cat_id) ON subcategories.subcat_id = ads.subcat_id) ON users.user_id = ads.user_id;
You might think about making your fieldnames all lower or all upper, remembering the case when you're mixing case like that is a nightmare. Though MySQL may not care, I know other DBs are case sensitive and it's a major headache. Also you might think about using the same names for the ID fields like, ads.cat_id and categories.cat_id and ads.subcat_id and subcategories.subcat_id but those are just preference things.

Posted: Sat Apr 22, 2006 7:14 pm
by BigAbe
Thanks for your help.

I tried the code exactly as you provided, and recieved this error:

Code: Select all

#1066 - Not unique table/alias: 'ads'
I have no clue what this means...

Posted: Sat Apr 22, 2006 7:22 pm
by shoebappa
Which code there's three above...

Posted: Sat Apr 22, 2006 7:28 pm
by shoebappa
I also notice there's an inconsistent users.userUID and users.userID so make sure they match above. Not sure which you really use.

Posted: Sat Apr 22, 2006 7:35 pm
by BigAbe
shoebappa wrote:Which code there's three above...
My apologies. I was referring to visionmaster.

Thanks a bunch shoebappa!

I had to reformat my code like this:

Code: Select all

SELECT ads.aduser, ads.adID, ads.adTitle, ads.adCategoryID, ads.adSubcategoryID, categories.catName, subcategories.subcatName
FROM users
INNER JOIN (
subcategories
INNER JOIN (
categories
INNER JOIN ads ON categories.catID = ads.adCategoryID
) ON subcategories.subcatID = ads.adSubcategoryID
) ON users.userID = ads.adUser
But it worked to perfection!

Thanks for your help,

-- Abe --

Posted: Sat Apr 22, 2006 7:49 pm
by shoebappa
I'm pretty sure it should work the first way I posted (your first way) which is usually easier to come up with by hand. I'm pretty sure it's just short hand for the inner join thing, but whatever works : )

When I start getting into more complex joins and crap I tend to go to MS Access Design View then SQL View and pop that into PHP, usually takes some tweaking but works. Might not be the most efficient, I don't know, but it saves me a lot of headaches.