Subquery help needed

The 2.0.x discussion forum has been locked; this will remain read-only. The 3.0.x discussion forum has been renamed phpBB Discussion.
Locked
User avatar
Hellweed
Registered User
Posts: 29
Joined: Thu May 29, 2003 10:04 pm

Subquery help needed

Post by Hellweed » Wed Jan 14, 2004 4:56 pm

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
Registered User
Posts: 2830
Joined: Tue Nov 27, 2001 10:42 pm

Post by R45 » Wed Jan 14, 2004 6:58 pm

Code: Select all

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

User avatar
Hellweed
Registered User
Posts: 29
Joined: Thu May 29, 2003 10:04 pm

Post by Hellweed » Wed Jan 14, 2004 9:47 pm

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
Registered User
Posts: 2830
Joined: Tue Nov 27, 2001 10:42 pm

Post by R45 » Wed Jan 14, 2004 9:59 pm

I didn't say do that :D

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;

User avatar
Hellweed
Registered User
Posts: 29
Joined: Thu May 29, 2003 10:04 pm

Post by Hellweed » Thu Jan 15, 2004 3:16 am

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. :D

R45
Registered User
Posts: 2830
Joined: Tue Nov 27, 2001 10:42 pm

Post by R45 » Thu Jan 15, 2004 12:39 pm

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;
}

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun » Thu Jan 15, 2004 2:13 pm

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)
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

R45
Registered User
Posts: 2830
Joined: Tue Nov 27, 2001 10:42 pm

Post by R45 » Thu Jan 15, 2004 2:32 pm

drathbun wrote: 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.

User avatar
Hellweed
Registered User
Posts: 29
Joined: Thu May 29, 2003 10:04 pm

Post by Hellweed » Thu Jan 15, 2004 3:08 pm

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...

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun » Thu Jan 15, 2004 7:14 pm

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)
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

User avatar
Hellweed
Registered User
Posts: 29
Joined: Thu May 29, 2003 10:04 pm

Post by Hellweed » Fri Jan 16, 2004 4:10 pm

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!

Locked

Return to “2.0.x Discussion”