473,320 Members | 2,035 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Replacing subselect with joins

Hi.

I have a query that looks like this:

SELECT user_id, user_given_names, user_surname, user_cast FROM users WHERE
user_id NOT IN (SELECT user_id FROM linkage WHERE group_id=1)
ORDER BY users.user_surname

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
Jul 20 '05 #1
5 2434
Mattias Nordstrom wrote:
SELECT user_id, user_given_names, user_surname, user_cast FROM users WHERE
user_id NOT IN (SELECT user_id FROM linkage WHERE group_id=1)
ORDER BY users.user_surname


select user_id, user_given_names, user_surname, user_cast
from users
left join linkage on linkage.user_id=users.user_id
and linkage.group_id=1
where linkage.user_id is null;
Jul 20 '05 #2
On Fri, 18 Jun 2004 21:02:03 +0000, Aggro wrote:
Mattias Nordstrom wrote:
SELECT user_id, user_given_names, user_surname, user_cast FROM users WHERE
user_id NOT IN (SELECT user_id FROM linkage WHERE group_id=1)
ORDER BY users.user_surname


select user_id, user_given_names, user_surname, user_cast
from users
left join linkage on linkage.user_id=users.user_id
and linkage.group_id=1
where linkage.user_id is null;


Thanks! That works.

--
Mattias Nordstrom
Jul 20 '05 #3
On Fri, 18 Jun 2004 21:02:03 +0000, Aggro wrote:
Mattias Nordstrom wrote:
SELECT user_id, user_given_names, user_surname, user_cast FROM users WHERE
user_id NOT IN (SELECT user_id FROM linkage WHERE group_id=1)
ORDER BY users.user_surname


select user_id, user_given_names, user_surname, user_cast
from users
left join linkage on linkage.user_id=users.user_id
and linkage.group_id=1
where linkage.user_id is null;


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

Jul 20 '05 #4
Mattias Nordstrom wrote:
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.


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_surname
-> from users
-> left join linkage on linkage.user_id=users.user_id
-> and linkage.group_id=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 misunderstanding something or have I
missed anything?

Can you provide a similar test case that shows how this doesn't work?
Jul 20 '05 #5


SELECT u.user_id, u.user_given_names, 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(CASE WHEN group_id = 1 THEN 0 ELSE 1 END)
) uid
WHERE
u.user_id = uid.user_id
ORDER BY u.user_surname

"Mattias Nordstrom" <ma***@use.net> wrote in message
news:pa****************************@use.net...
Hi.

I have a query that looks like this:

SELECT user_id, user_given_names, user_surname, user_cast FROM users WHERE
user_id NOT IN (SELECT user_id FROM linkage WHERE group_id=1)
ORDER BY users.user_surname

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


Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Steven Stern | last post by:
I'm stuck on a host that is still running MYSQL version 3. I need to flatten out a relationship to AND a set of criteria. In a nutshell User UserID UserEmail UserOther
0
by: limbert | last post by:
------=_NextPart_000_0001_01C34D7B.DFBF53C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, =20 I was reading the manual and it said that...
1
by: Magnus | last post by:
Hi, I have previously worked mostly with Sql Server and wonder how to write a subselect query in mysql 4.0. What i would like to do is something like: select a.name, b.isbn from author a...
7
by: Alex | last post by:
Hi, I have some problems with creating a query that will replace values in one table from another one. Table 1: userName : refCode1 : refCode2 ------------------------------ alex : 12 ...
3
by: Neil Zanella | last post by:
Hello, I would like to ask the about the following... PostgreSQL allows tables resulting from subselects to be renamed with an optional AS keyword whereas Oracle 9 will report an error...
4
by: James | last post by:
I have a performance problem with the following query and variations on the subselect. The EXISTS version of the first example will complete in ~10 minutes. The NOT logic in both the examples...
2
by: Morten K. Poulsen | last post by:
(re-post) Dear list, Please let me know if this is not the list to ask this kind of question. I am trying to optimize a query that joins two relatively large (750000 rows in each) tables....
6
by: Sebastien | last post by:
I have the following statement which I run successfully in... 1 hour 10 minutes. SELECT a.tsgicd as ACCT_ID, a.tsa5cd as SEC_ID, CASE WHEN (SUBSTRING(a.tsgicd, 6, 1) = 'R' or...
0
by: 23db | last post by:
I have the following three tables: OFFICE, EMPLOYEE, SYSTEM. OFFICE joins to EMPLOYEE on OFFICE_ID (OFFICE can have zero to many EMPLOYEE's), SYSTEM joins to EMPLOYEE on EMPLOYEE_ID and OFFICE_ID...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.