Question about SELECT statement

[michael123]

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.

[campkev]

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

[michael123]

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

[campkev]

upgrade to 4.1?

[michael123]

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.

[campkev]

can you do joins?

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

[michael123]

still have problem.
For example:

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

[Kate Albany]

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 :)

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]

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