SQL syntax help

[Tryfwar]

I was hoping that somebody might be able to help me with this problem:

Firstly, I have created a new table in my forums db called userclass and in it there are 2 fields: userclassid & title.

Then i have added a field to the user table called 'userclassid' as well and have successfully been able to modify the user.php to enable me to assign a class title to each user.

In addition to this i have added a field to the usergroup table called 'rollcall' which is simply a 1 or 0, so i can assign wether or not i wish for these usergroups to appear in the rollcall. (just like the showgroups function does for the showgroups.php)

The problem i have is in this query that was originaly from the showgroups.php, i cannot work out how i should write it :(

I would like it so that it will list users in groups of class and only those who have been assigned '1' in the usergroup.rollcall table.

The below Query displays them almost as i want but it lists each user 5 times!! lol oh and it also seems to display users regardless of the WHERE clause :/


$users = $DB_site->query("
SELECT
$classfieldselect userclass.title, user.userid, user.username, user.userclassid, user.userclassid,
user.usergroupid
FROM userclass
LEFT JOIN user ON (userclass.userclassid = user.userclassid)
LEFT JOIN userfield ON (userfield.userid = user.userid)
LEFT JOIN usergroup ON usergroup.rollcall
WHERE usergroup.rollcall = 1
");

Thanks in advance to anyone who can help :)

[Xenon]

try that one:

$users = $DB_site->query("
SELECT DISTINCT
$classfieldselect userclass.title, user.userid, user.username, user.userclassid, user.userclassid,
user.usergroupid
FROM user
LEFT JOIN userclass ON (userclass.userclassid = user.userclassid)
LEFT JOIN userfield ON (userfield.userid = user.userid)
LEFT JOIN usergroup ON (usergroup.usergroupid = user.usergroupid)
WHERE usergroup.rollcall = 1
");

[Tryfwar]

woah! nice one !!! :)

Works a treat!!! :D :D :D

Thanks alot Xenon :) ... juuuuuust one more thing: its in reverse alphabetical order, anyway to switch that ? :)

The usernames are i mean.

[Xenon]

welcome :)

add ORDER BY username ASC after the where clause ;)

[Tryfwar]

Thanks a million :) made me very happy :)

[Tryfwar]

how can i make it so that it WONT list members who haven't yet got a 'userclassid' please?

I've tried using the NOT 0 syntax but i keep getting errors :(

[Xenon]

use that where:WHERE usergroup.rollcall = 1 AND user.userclassid != 0

[Tryfwar]

SELECT DISTINCT
userclass.title, user.userid, user.gender, user.username, user.userclassid,
user.usergroupid
FROM user
LEFT JOIN userclass ON (userclass.userclassid = user.userclassid !=0)
LEFT JOIN userfield ON (userfield.userid = user.userid)
LEFT JOIN usergroup ON (usergroup.usergroupid = user.usergroupid)
WHERE usergroup.rollcall = 1 ORDER BY username ASC
");


just worked it out!
err its different to yours

[Xenon]

use mine :)

[Tryfwar]

have hehe, thanks again Xenon, your a star :)

[Xenon]

:)

you're welcome