rewriting a subquery

[steviesackin]

Hi,

I have been trying to rewrite the following
query without the subquery but my head just exploded.
Any help most gratefully received.

Stevie

SELECT Users.*, Groups.GroupName
FROM (Users
LEFT JOIN GroupUsers ON Users.UserID = GroupUsers.UserID)
LEFT JOIN Groups ON GroupUsers.GroupID = Groups.GroupID
WHERE Users.UserID not in
(SELECT CourseUsers.UserID FROM CourseUsers
WHERE TopicID=1)
ORDER BY Users.Name

 

[newphpbie]

I had similar issues because the ver of MySQL I'm running doesn't accept subqueries....

I found this really useful...

http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html

 

[steviesackin]

Thanks for that.

I now know where I need to get to but my map's upsidedown
and inside out. This is my latest effort (which is wrong):

SELECT Users.*, Groups.GroupName FROM Users
LEFT JOIN GroupUsers ON Users.UserID = GroupUsers.UserID
LEFT JOIN Groups ON GroupUsers.GroupID = Groups.GroupID
LEFT JOIN CourseUsers ON Users.UserID = CourseUsers.UserID
WHERE CourseUsers.UserID IS NULL and CourseUsers.TopicID=1
ORDER BY Users.Name

 

[hos2]

if subqueries aren't working perhaps you can first make an string of the values you want to leave out

SELECT CourseUsers.UserID FROM CourseUsers
WHERE TopicID=1
$rs2=mysql_query($query,$conn);

$k=0;
$list = mysql_num_rows($rs2);
while($k < $list) {
$row = mysql_fetch_array($rs2);
$userid=$row[&quot;userid&quot;];
$leaveout=$leaveout . $userid . ', ';
$k++;
}

and then
SELECT Users.*, Groups.GroupName
FROM (Users
LEFT JOIN GroupUsers ON Users.UserID = GroupUsers.UserID)
LEFT JOIN Groups ON GroupUsers.GroupID = Groups.GroupID
WHERE Users.UserID not in (&quot;.$leaveout'.&quot;')&quot;;

you must add the singlequotes in the string
$leaveout=$leaveout . '''' . $userid . ''', ';
$leaveout=$leaveout . '\''$userid . '\', ';

sorry don't know the exact syntax to add ' to a string

and then at the end get rid of the last &quot;,&quot;
$leaveout=substr($leaveout,0,strlen($leaveout)-1);

 

[skiflyer]

SELECT Users.*, Groups.GroupName
FROM (Users
LEFT JOIN GroupUsers ON Users.UserID = GroupUsers.UserID)
LEFT JOIN Groups ON GroupUsers.GroupID = Groups.GroupID
WHERE Users.UserID not in
(SELECT CourseUsers.UserID FROM CourseUsers
WHERE TopicID=1)
ORDER BY Users.Name

Taking a stab.. giving you my stream of thought...
First drop all the LEFT JOINs and figure out how to get your list of USERID's... I believe what you want is...

SELECT CourseUsers.UserID 
FROM CourseUsers
WHERE TopicID != 1

Ok, so now you want to start with that list, and left join it to the others, am I right?

SELECT Users.*, Groups.GroupName
FROM CourseUsers
LEFT JOIN Users ON (Users.UserID = CourseUsers.UserID)
LEFT JOIN GroupUsers ON (Users.UserID = CourseUsers.UserID)
LEFT JOIN Groups ON (GroupUsers.GroupID = Groups.GroupID)
WHERE CourseUsers.TopicID != 1
ORDER BY Users.Name

Hope I followed properly,
Rob

 

[steviesackin]

hos2, you are a star - it works beautifully. thank you.

stevie