Ad:

MySQL Discussion Thread
Unsolved
Views: 4581

Sep 30th, 2005

Question about SELECT statement

Expand Post »

In database I have three tables: t1,t2,t3
all three tables contain the same field (username)
t1(username)
-------
user1
user2
user3
user4
user5

t2(username)
-------
user3
user4

t3(username)
-------
user2

Now how can I write SELECT statment to retrieve all username from table1(t1) which is NOT included in table2(t2) and table3(t3)? thanks.

Similar Threads

Question about SELECT statement in MS SQL
Hi,Need help on sql select statement structure in MS SQL
Question about select statement in MySQL
select statemt LIKE in MySQL
Getting current value in Select Statement in MS SQL

michael123

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

93 posts
since Jun 2005

Permalink

Sep 30th, 2005

Re: Question about SELECT statement

select username from t1 where username not in (select username from t2) and username not in (select username from t3)

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

Permalink

Sep 30th, 2005

Re: Question about SELECT statement

I use Mysql 3.23 server, I think it doesn't support sub-select statement, is there alternative to do that? thanks.

michael123

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

93 posts
since Jun 2005

Permalink

Sep 30th, 2005

Re: Question about SELECT statement

upgrade to 4.1?

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

Permalink

Sep 30th, 2005

Re: Question about SELECT statement

I use hosting services, so it's impossible for me to upgrade it, I am just wondering if there a way to do it, except using sub-select statement.

michael123

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

93 posts
since Jun 2005

Permalink

Sep 30th, 2005

Re: Question about SELECT statement

can you do joins?

select * from t1
inner join t2 on t1.userid != t2.userid
inner join t3 on t1.userid !- t3.userid

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

Permalink

Oct 1st, 2005

Re: Question about SELECT statement

still have problem.
For example:

MySQL Syntax (Toggle Plain Text)
mysql> SELECT uname FROM authuser;
+--------+
| uname  |
+--------+
| user1  |
| user12 |
| user2  |
| user3  |
+--------+
4 rows IN SET (0.00 sec)
 
mysql> SELECT uname FROM memberhist;
+-------+
| uname |
+-------+
| user1 |
+-------+
1 row IN SET (0.00 sec)
 
mysql> SELECT DISTINCT a.uname FROM authuser a,memberhist b
    -> WHERE a.uname<>b.uname;
+--------+
| uname  |
+--------+
| user12 |
| user2  |
| user3  |
+--------+
3 rows IN SET (0.00 sec)
mysql> select uname from authuser;
+--------+
| uname  |
+--------+
| user1  |
| user12 |
| user2  |
| user3  |
+--------+
4 rows in set (0.00 sec)

mysql> select uname from memberhist;
+-------+
| uname |
+-------+
| user1 |
+-------+
1 row in set (0.00 sec)

mysql> select distinct a.uname from authuser a,memberhist b
    -> where a.uname<>b.uname;
+--------+
| uname  |
+--------+
| user12 |
| user2  |
| user3  |
+--------+
3 rows in set (0.00 sec)

If table "memberhist" has only one record, then I can get correct result(find users which in table "authuser" but not in "memberhist"), however if I add a new entry to "memberhist", then the result is wrong, see below:

MySQL Syntax (Toggle Plain Text)
mysql> SELECT uname FROM authuser;
+--------+
| uname  |
+--------+
| user1  |
| user12 |
| user2  |
| user3  |
+--------+
4 rows IN SET (0.00 sec)
 
 
mysql> SELECT uname FROM memberhist;
+-------+
| uname |
+-------+
| user1 |
| user3 |
+-------+
2 rows IN SET (0.01 sec)
 
 
mysql> SELECT DISTINCT a.uname FROM authuser a,memberhist b
    -> WHERE a.uname<>b.uname;
+--------+
| uname  |
+--------+
| user12 |
| user2  |
| user3  |
| user1  |
+--------+
4 rows IN SET (0.00 sec)
mysql> select uname from authuser;
+--------+
| uname  |
+--------+
| user1  |
| user12 |
| user2  |
| user3  |
+--------+
4 rows in set (0.00 sec)


mysql> select uname from memberhist;
+-------+
| uname |
+-------+
| user1 |
| user3 |
+-------+
2 rows in set (0.01 sec)


mysql> select distinct a.uname from authuser a,memberhist b
    -> where a.uname<>b.uname;
+--------+
| uname  |
+--------+
| user12 |
| user2  |
| user3  |
| user1  |
+--------+
4 rows in set (0.00 sec)

Suppose to get the result of "user12,user2", but it shows "user12,user2,user3,user1".

Don't know how to fix it.

michael123

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

93 posts
since Jun 2005

Permalink

Oct 1st, 2005

Re: Question about SELECT statement

Hello,

The reason that your query does not work correctly is because by using <> in this manner you are creating a 'partial' Cartesian Join between the two tables. In doing so the query returns you a 'partial' Cartesian Product. Though this fact is masked from you in your first example by the use of 'distinct'. Run your query using 'select *' and you will see what is really happening. Google for Cartesian Join/Cartesian Product to find out more.

The example below is in Oracle syntax. So I suspect you will have to translate the (+) syntax for use with MySQL. It is an inner or outer join, I can never remember which. I just know which side of the join to place the (+) when needed

MySQL Syntax (Toggle Plain Text)
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP AND Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
 
SQL> SET feedback on;
SQL> SELECT * FROM users1;
 
USERNAME
--------------------------------------------------
user1
user2
user3
user4
user5
user6
 
6 rows selected.
 
SQL> SELECT * FROM users2;
 
USERNAME
--------------------------------------------------
user3
user4
user6
 
3 rows selected.
 
SQL> SELECT   DISTINCT(u1.username)
  2  FROM	 users1 u1,
  3		   users2 u2
  4  WHERE	u1.username = u2.username(+)
  5  AND	  u2.username IS NULL;
 
USERNAME
--------------------------------------------------
user1
user2
user5
 
3 rows selected.
 
SQL> DELETE FROM users2 u2 WHERE u2.username = 'user3';
 
1 row deleted.
 
SQL> SELECT   DISTINCT(u1.username)
  2  FROM	 users1 u1,
  3		   users2 u2
  4  WHERE	u1.username = u2.username(+)
  5  AND	  u2.username IS NULL;
 
USERNAME
--------------------------------------------------
user1
user2
user3
user5
 
4 rows selected.
 
SQL> DELETE FROM users2;
 
2 rows deleted.
 
SQL> SELECT   DISTINCT(u1.username)
  2  FROM	 users1 u1,
  3		   users2 u2
  4  WHERE	u1.username = u2.username(+)
  5  AND	  u2.username IS NULL;
 
USERNAME
--------------------------------------------------
user1
user2
user3
user4
user5
user6
 
6 rows selected.
 
SQL> ROLLBACK;
 
ROLLBACK complete.
 
SQL> SELECT   DISTINCT(u1.username)
  2  FROM	 users1 u1,
  3		   users2 u2
  4  WHERE	u1.username = u2.username(+)
  5  AND	  u2.username IS NULL;
 
USERNAME
--------------------------------------------------
user1
user2
user5
 
3 rows selected.
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> set feedback on;
SQL> select * from users1;

USERNAME
--------------------------------------------------
user1
user2
user3
user4
user5
user6

6 rows selected.

SQL> select * from users2;

USERNAME
--------------------------------------------------
user3
user4
user6

3 rows selected.

SQL> select   distinct(u1.username)
  2  from	 users1 u1,
  3		   users2 u2
  4  where	u1.username = u2.username(+)
  5  and	  u2.username is null;

USERNAME
--------------------------------------------------
user1
user2
user5

3 rows selected.

SQL> delete from users2 u2 where u2.username = 'user3';

1 row deleted.

SQL> select   distinct(u1.username)
  2  from	 users1 u1,
  3		   users2 u2
  4  where	u1.username = u2.username(+)
  5  and	  u2.username is null;

USERNAME
--------------------------------------------------
user1
user2
user3
user5

4 rows selected.

SQL> delete from users2;

2 rows deleted.

SQL> select   distinct(u1.username)
  2  from	 users1 u1,
  3		   users2 u2
  4  where	u1.username = u2.username(+)
  5  and	  u2.username is null;

USERNAME
--------------------------------------------------
user1
user2
user3
user4
user5
user6

6 rows selected.

SQL> rollback;

Rollback complete.

SQL> select   distinct(u1.username)
  2  from	 users1 u1,
  3		   users2 u2
  4  where	u1.username = u2.username(+)
  5  and	  u2.username is null;

USERNAME
--------------------------------------------------
user1
user2
user5

3 rows selected.

Hope that helps

Kate

Kate Albany

Reputation Points: 10

Solved Threads: 1

Junior Poster in Training

Offline

71 posts
since Jun 2005

Permalink

Oct 1st, 2005

Re: Question about SELECT statement

It is an ugly solution I would not personally use in Oracle, though as you say you cannot use sub-querys, I know of on better method.

Also, in my example you do not need to use the 'distinct' keyword, but it may save you problems down the line should you ever get duplicate records in the table. Though a good index enforcing a unique constraint would stop that altheother

Kate

Kate Albany

Reputation Points: 10

Solved Threads: 1

Junior Poster in Training

Offline

71 posts
since Jun 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: arrange date&time column

Next Thread in MySQL Forum Timeline: Team of Web developers is looking for customers

DaniWeb Message

Cancel Changes

User Name
Password