SQL Query Help Needed :)?

Ok brief overview. For this part there are 2 main tables: user_cat and category.

category table is simply the names of categories and a corresponding is eg:

catid catname
1 fruit
2 veg
3 sweets
etc

The user_cat table holds user id and a cat id to assign them a or multipl categories eg

user_cat
userid catid
1 2
1 3
2 1
3 1
3 2

etc etc.




Now the SQL I want is to select the categories a user belongs to based on the user id. So here is what I have currently:

SELECT CATNAME
FROM CATEGORY, USER_CAT, SITE_USER
WHERE USER_CAT.USERID = #url.id# AND CATEGORY.CATID = USER_CAT.CATID

Which in fairness when tested does give a list but it lists all categories a cust is linked with about ten times and using UNIQUE in the SQL does not work. How can I make it so that the category name is only selected once seeing as the UNIQUE isn't working.

(Another part of this issue in further details :P)

Ultimately what I want to do is print out a list of the categories of a user. As it stands now with the current setup described above, the print statement is printing out only one of the users categories even though it should loop through the resultset from the SQL.

I imagine though that once the previous problem is fixed everything else should follow suit but this is just to give you more info on the problem.

You only described one join: between category and user_cat. You need to join site_user too.

SELECT CATNAME
FROM CATEGORY, USER_CAT, SITE_USER
WHERE USER_CAT.USERID = #url.id# AND CATEGORY.CATID = USER_CAT.CATID
AND USER_CAT.USERID = SITE_USER.USERID

Without that last join, SQL does a cartesian product between the current user's categories and ALL entries in the user table.

In fact, you don't even need to include the site_user table because you're not even using it!

SELECT CATNAME
FROM CATEGORY, USER_CAT
WHERE USER_CAT.USERID = #url.id# AND CATEGORY.CATID = USER_CAT.CATID

You are missing a join. The site_user table needs to be joined to the user_cat table.

You can either remove the site_user table from your FROM clause or add "AND user_cat.user_id = site_user.user_id" to your WHERE clause.