Replacing subselect with joins

[Mattias Nordstrom]

Hi.

I have a query that looks like this:

SELECT user_id, user_given_name s, user_surname, user_cast FROM users WHERE
user_id NOT IN (SELECT user_id FROM linkage WHERE group_id=1)
ORDER BY users.user_surn ame

What this does is return the users who are not in group 1. linkage
contains user_id, group_id pairs of users who are in what groups. What I
would like to do is write this not using subselects, as they are not
supported in older mysql versions.

So, is it possible to write a query (1 query) using mysql 3.x or 4.0 SQL
that will return the users that don't exist in a specific group when you
have the following table structure (simplified for this example):

users:
user_id

linkage:
user_id, group_id

Users can exists in several groups, that is why I use the linkage table,
otherwise it could be done in an easier way. I tried to experiment using
some joins but I'm stuck. Is subselects the only way? Writing 2 queries is
possible, of course, but I'd rather have it all in one.

Thanks in advance.

--
Mattias Nordstrom

[Aggro]

Re: Replacing subselect with joins

Mattias Nordstrom wrote:
[color=blue]
> SELECT user_id, user_given_name s, user_surname, user_cast FROM users WHERE
> user_id NOT IN (SELECT user_id FROM linkage WHERE group_id=1)
> ORDER BY users.user_surn ame[/color]

select user_id, user_given_name s, user_surname, user_cast
from users
left join linkage on linkage.user_id =users.user_id
and linkage.group_i d=1
where linkage.user_id is null;

[Mattias Nordstrom]

Re: Replacing subselect with joins

On Fri, 18 Jun 2004 21:02:03 +0000, Aggro wrote:
[color=blue]
> Mattias Nordstrom wrote:
>[color=green]
>> SELECT user_id, user_given_name s, user_surname, user_cast FROM users WHERE
>> user_id NOT IN (SELECT user_id FROM linkage WHERE group_id=1)
>> ORDER BY users.user_surn ame[/color]
>
> select user_id, user_given_name s, user_surname, user_cast
> from users
> left join linkage on linkage.user_id =users.user_id
> and linkage.group_i d=1
> where linkage.user_id is null;[/color]

Thanks! That works.

--
Mattias Nordstrom

[Mattias Nordstrom]

Re: Replacing subselect with joins

On Fri, 18 Jun 2004 21:02:03 +0000, Aggro wrote:
[color=blue]
> Mattias Nordstrom wrote:
>[color=green]
>> SELECT user_id, user_given_name s, user_surname, user_cast FROM users WHERE
>> user_id NOT IN (SELECT user_id FROM linkage WHERE group_id=1)
>> ORDER BY users.user_surn ame[/color]
>
> select user_id, user_given_name s, user_surname, user_cast
> from users
> left join linkage on linkage.user_id =users.user_id
> and linkage.group_i d=1
> where linkage.user_id is null;[/color]

No, apparently that returns all users. I'd like it to return only the ones
that are not in group_id=1. i.e. should not return the ones that are in
group_id=1, which it does now.

Any ideas?

--
Mattias Nordstrom

[Aggro]

Re: Replacing subselect with joins

Mattias Nordstrom wrote:
[color=blue]
> No, apparently that returns all users. I'd like it to return only the ones
> that are not in group_id=1. i.e. should not return the ones that are in
> group_id=1, which it does now.[/color]

There was one error in my query, which does give syntax error atleast on
MySQL version 3.x, but after fixing that, it should work correctly.
Let me show you (I only put user_id and user_surname to users table,
other columns should follow them automaticly, so adding columns won't
make a difference.):

-----start from mysql console----------------------------------
mysql> create table users(user_id int, user_surname varchar(255) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into users values(1,'a'),( 2,'b'),(3,'c'),
-> (4,'d'),(5,'e') ,(6,'f'),(7,'g' );
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> create table linkage(user_id int, group_id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into linkage values(1,1),(1, 2),(1,3),(2,2),
-> (2,3),(2,4),(3, 1),(4,2),(6,1), (7,2),(7,4);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0

mysql> select * from users;
+---------+--------------+
| user_id | user_surname |
+---------+--------------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
+---------+--------------+
7 rows in set (0.00 sec)

mysql> select * from linkage;
+---------+----------+
| user_id | group_id |
+---------+----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 4 | 2 |
| 6 | 1 |
| 7 | 2 |
| 7 | 4 |
+---------+----------+
11 rows in set (0.00 sec)

mysql> select users.user_id, users.user_surn ame
-> from users
-> left join linkage on linkage.user_id =users.user_id
-> and linkage.group_i d=1
-> where linkage.user_id is null;
+---------+--------------+
| user_id | user_surname |
+---------+--------------+
| 2 | b |
| 4 | d |
| 5 | e |
| 7 | g |
+---------+--------------+
4 rows in set (0.00 sec)
-----end from mysql console----------------------------------

We had 7 users, but only 4 of them are returned, none of the users which
are in group 1 are returned. Am I misunderstandin g something or have I
missed anything?

Can you provide a similar test case that shows how this doesn't work?

[Isaac Blank]

Re: Replacing subselect with joins



SELECT u.user_id, u.user_given_na mes, u.user_surname, u.user_cast
FROM users u, (SELECT user.user_id
FROM user, linkage
WHERE linkage.user_id =user.user_id
GROUP BY user.user_id
HAVING count(*)=SUM(CA SE WHEN group_id = 1 THEN 0 ELSE 1 END)
) uid
WHERE
u.user_id = uid.user_id
ORDER BY u.user_surname

"Mattias Nordstrom" <matta@use.ne t> wrote in message
news:pan.2004.0 6.18.20.07.54.9 91188@use.net.. .[color=blue]
> Hi.
>
> I have a query that looks like this:
>
> SELECT user_id, user_given_name s, user_surname, user_cast FROM users WHERE
> user_id NOT IN (SELECT user_id FROM linkage WHERE group_id=1)
> ORDER BY users.user_surn ame
>
> What this does is return the users who are not in group 1. linkage
> contains user_id, group_id pairs of users who are in what groups. What I
> would like to do is write this not using subselects, as they are not
> supported in older mysql versions.
>
> So, is it possible to write a query (1 query) using mysql 3.x or 4.0 SQL
> that will return the users that don't exist in a specific group when you
> have the following table structure (simplified for this example):
>
> users:
> user_id
>
> linkage:
> user_id, group_id
>
> Users can exists in several groups, that is why I use the linkage table,
> otherwise it could be done in an easier way. I tried to experiment using
> some joins but I'm stuck. Is subselects the only way? Writing 2 queries is
> possible, of course, but I'd rather have it all in one.
>
> Thanks in advance.
>
> --
> Mattias Nordstrom
>
>
>[/color]