Subquery help needed

[Hellweed]

I'm a bit lost with the multiple tables involved. These select statements work individually, but what I want to achieve is something like:

SELECT all users from

Code: Select all

SELECT * FROM phpbb_users, phpbb_user_group
                WHERE phpbb_users.user_id=phpbb_user_group.user_id
                 AND phpbb_user_group.group_id='233'

Who are not in

Code: Select all

SELECT * FROM phpbb_users, phpbb_user_group
                WHERE phpbb_users.user_id=phpbb_user_group.user_id
                 AND phpbb_user_group.group_id='226'

ORDER BY phpbb_users.username



Any ideas would be helpful.

Thanks in advance

[R45]

Code: Select all

SELECT whatever FROM whatever WHERE column NOT IN 
   ( SELECT column FROM whatever WHERE whatever )
      ORDER BY whatever

[Hellweed]

Still struggling. This is what I have so far but I get errors.

Code: Select all

SELECT * From
                        (SELECT * FROM phpbb_users, phpbb_user_group
                                WHERE phpbb_users.user_id=phpbb_user_group.user_id
                                AND phpbb_user_group.group_id='233')
                        WHERE user_id NOT IN
                        (SELECT * FROM phpbb_users, phpbb_user_group
                                WHERE phpbb_users.user_id=phpbb_user_group.user_id
                                AND phpbb_user_group.group_id='226')
                        ORDER BY phpbb_users.username

[R45]

I didn't say do that

I take it you want to do..

Code: Select all

SELECT *
	FROM phpbb_users
		WHERE user_id IN 
			( 
				SELECT u.user_id
					FROM phpbb_users AS u, phpbb_user_group AS ug
						WHERE u.user_id = ug.user_id
							AND ug.group_id = 233
			)
		   AND user_id NOT IN
			(
				SELECT u.user_id
					FROM phpbb_users AS u, phpbb_user_group AS ug
						WHERE u.user_id = ug.user_id
							AND ug.group_id = 226
			)
	ORDER BY username;

[Hellweed]

I think we're much closer. All I’ve done is plug the SELECT statement into my basic code for getting data. I've used it dozens of times so far.

Code: Select all

<?
include ("conf.inc.php");
$connect= mysql_connect($host,$login,$pass);
mysql_select_db($base, $connect);

$query= "SELECT * 
	FROM phpbb_users 
	WHERE user_id IN 
		( 
		SELECT u.user_id 
		FROM phpbb_users AS u, phpbb_user_group AS ug 
			WHERE u.user_id = ug.user_id 
				AND ug.group_id = 233
		) 
		AND user_id NOT IN 
		( 
			SELECT u.user_id 
			FROM phpbb_users AS u, phpbb_user_group AS ug 
			WHERE u.user_id = ug.user_id 
			AND ug.group_id = 226
		) 
		ORDER BY 
			username";

$result=mysql_query($query, $connect);

	while($news=mysql_fetch_array($result))
      	{
		echo $news['username']; 
            }
?>

It get all the way through to the WHILE statement and I get this error

Code: Select all

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in (page and line number)

I know it should get results. Would the way I get the result be causing this error?

Thanks again for your time. I'm looking at other recourses as well. I'll let ya know if I get it figured out.

[R45]

Are you sure there are any results?

Before you fetch the data, do

Code: Select all

if(mysql_num_rows($result)==0)
{
    echo 'No results';
    die;
}

[drathbun]

Minor point.

You don't need the "users" table in your sub-query. The user_id is already in the user_groups table, so there is no point to join the two tables. Just use this:

Code: Select all

$query= "SELECT * 
   FROM phpbb_users 
   WHERE user_id IN 
      ( 
      SELECT ug.user_id 
      FROM phpbb_user_group AS ug 
         WHERE ug.group_id = 233 
      ) 
      AND user_id NOT IN 
      ( 
         SELECT ug.user_id 
         FROM phpbb_user_group AS ug 
         WHERE ug.group_id = 226 
      ) 
      ORDER BY 
         username"; 

BTW, I assume you're not using MySQL, as I believe sub-queries are not supported until version 5. And if you're using a database that supports the MINUS or EXCEPT operation (check your documentation) try this; it's faster, and much more convenient:

Code: Select all

$query= "
     SELECT ug.user_id 
      FROM phpbb_user_group AS ug 
         WHERE ug.group_id = 233 
     MINUS
         SELECT ug.user_id 
         FROM phpbb_user_group AS ug 
         WHERE ug.group_id = 226 "; 

That gives you a set of user_id values, and you can then process each one as desired. 8)

[R45]

And if you're using a database that supports the MINUS or EXCEPT operation (check your documentation) try this; it's faster, and much more convenient:

Indeed, forgot about that. EXCEPT is PostgreSQL... I believe MINUS is Oracle and MSSQL, but I'm basing that off of bad memory.

[Hellweed]

I am using MySQL. I'll have to check what version, but I bet that is the problem.

EDIT: MySQL 4

Rats, I'd hate to have to hard code all this...

[drathbun]

Run the first query, load user_id values into an array.
Repeat for second query.
Use the array_diff() function to find the difference. 8)

[Hellweed]

Manipulating Arrays are a bit over my head at this point, but I did find a solution to my query problem. I added the user_rank as additional criteria to limit the record population.

Code: Select all

$query= "SELECT DISTINCT *
                FROM phpbb_users, phpbb_user_group, phpbb_ranks
                WHERE phpbb_users.user_id=phpbb_user_group.user_id
                AND  phpbb_users.user_rank=phpbb_ranks.rank_id
                AND ((phpbb_user_group.group_id='225') OR (phpbb_user_group.group_id='226'))
                AND ((phpbb_users.user_rank='9') OR (phpbb_users.user_rank='8'))
                ORDER BY
                        phpbb_users.username";

Thanks again for your time and effort trying to help solve my problem.
This place is the best!