•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 401,693 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,687 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Views: 2850 | Replies: 8
 |
•
•
Join Date: Jun 2005
Posts: 85
Reputation: 
Rep Power: 4
Solved Threads: 0
Junior Poster in Training
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.
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.
•
•
Join Date: Jul 2005
Location: Dallas, TX
Posts: 481
Reputation:
Rep Power: 4
Solved Threads: 19
Posting Pro in Training
select username from t1 where username not in (select username from t2) and username not in (select username from t3)
•
•
Join Date: Jun 2005
Posts: 85
Reputation:
Rep Power: 4
Solved Threads: 0
Junior Poster in Training
I use Mysql 3.23 server, I think it doesn't support sub-select statement, is there alternative to do that? thanks.
•
•
Join Date: Jul 2005
Location: Dallas, TX
Posts: 481
Reputation:
Rep Power: 4
Solved Threads: 19
Posting Pro in Training
upgrade to 4.1?
•
•
Join Date: Jun 2005
Posts: 85
Reputation:
Rep Power: 4
Solved Threads: 0
Junior Poster in Training
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.
•
•
Join Date: Jul 2005
Location: Dallas, TX
Posts: 481
Reputation:
Rep Power: 4
Solved Threads: 19
Posting Pro in Training
can you do joins?
select * from t1
inner join t2 on t1.userid != t2.userid
inner join t3 on t1.userid !- t3.userid
select * from t1
inner join t2 on t1.userid != t2.userid
inner join t3 on t1.userid !- t3.userid
•
•
Join Date: Jun 2005
Posts: 85
Reputation:
Rep Power: 4
Solved Threads: 0
Junior Poster in Training
still have problem.
For example:
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:
Suppose to get the result of "user12,user2", but it shows "user12,user2,user3,user1".
Don't know how to fix it.
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.
•
•
Join Date: Jun 2005
Posts: 71
Reputation:
Rep Power: 4
Solved Threads: 0
Junior Poster in Training
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
Hope that helps
Kate
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.
Kate
•
•
Join Date: Jun 2005
Posts: 71
Reputation:
Rep Power: 4
Solved Threads: 0
Junior Poster in Training
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
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
|
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
Similar Threads
- Question about SELECT statement (MS SQL)
- Hi,Need help on sql select statement structure (MS SQL)
- Question about select statement (MySQL)
- select statemt LIKE (MySQL)
- Getting current value in Select Statement (MS SQL)
- Program Problem with a select statement to access Data base (C)
Other Threads in the MySQL Forum
- Previous Thread: arrange date&time column
- Next Thread: Team of Web developers is looking for customers
