Question about SELECT statement

Reply

•
•
Join Date: Jun 2005
Posts: 92
Reputation: michael123 is an unknown quantity at this point 
Solved Threads: 0
michael123 michael123 is offline Offline
Junior Poster in Training

Question about SELECT statement

 
0
  #1
Sep 30th, 2005
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2005
Posts: 483
Reputation: campkev is an unknown quantity at this point 
Solved Threads: 19
campkev campkev is offline Offline
Posting Pro in Training

Re: Question about SELECT statement

 
0
  #2
Sep 30th, 2005
select username from t1 where username not in (select username from t2) and username not in (select username from t3)
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2005
Posts: 92
Reputation: michael123 is an unknown quantity at this point 
Solved Threads: 0
michael123 michael123 is offline Offline
Junior Poster in Training

Re: Question about SELECT statement

 
0
  #3
Sep 30th, 2005
I use Mysql 3.23 server, I think it doesn't support sub-select statement, is there alternative to do that? thanks.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2005
Posts: 483
Reputation: campkev is an unknown quantity at this point 
Solved Threads: 19
campkev campkev is offline Offline
Posting Pro in Training

Re: Question about SELECT statement

 
0
  #4
Sep 30th, 2005
upgrade to 4.1?
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2005
Posts: 92
Reputation: michael123 is an unknown quantity at this point 
Solved Threads: 0
michael123 michael123 is offline Offline
Junior Poster in Training

Re: Question about SELECT statement

 
0
  #5
Sep 30th, 2005
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2005
Posts: 483
Reputation: campkev is an unknown quantity at this point 
Solved Threads: 19
campkev campkev is offline Offline
Posting Pro in Training

Re: Question about SELECT statement

 
0
  #6
Sep 30th, 2005
can you do joins?

select * from t1
inner join t2 on t1.userid != t2.userid
inner join t3 on t1.userid !- t3.userid
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2005
Posts: 92
Reputation: michael123 is an unknown quantity at this point 
Solved Threads: 0
michael123 michael123 is offline Offline
Junior Poster in Training

Re: Question about SELECT statement

 
0
  #7
Oct 1st, 2005
still have problem.
For example:
MySQL Syntax (Toggle Plain Text) 
  1. mysql> SELECT uname FROM authuser;
  2. +--------+
  3. | uname  |
  4. +--------+
  5. | user1  |
  6. | user12 |
  7. | user2  |
  8. | user3  |
  9. +--------+
  10. 4 rows IN SET (0.00 sec)
  11.  
  12. mysql> SELECT uname FROM memberhist;
  13. +-------+
  14. | uname |
  15. +-------+
  16. | user1 |
  17. +-------+
  18. 1 row IN SET (0.00 sec)
  19.  
  20. mysql> SELECT DISTINCT a.uname FROM authuser a,memberhist b
  21.     -> WHERE a.uname<>b.uname;
  22. +--------+
  23. | uname  |
  24. +--------+
  25. | user12 |
  26. | user2  |
  27. | user3  |
  28. +--------+
  29. 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) 
  1. mysql> SELECT uname FROM authuser;
  2. +--------+
  3. | uname  |
  4. +--------+
  5. | user1  |
  6. | user12 |
  7. | user2  |
  8. | user3  |
  9. +--------+
  10. 4 rows IN SET (0.00 sec)
  11.  
  12.  
  13. mysql> SELECT uname FROM memberhist;
  14. +-------+
  15. | uname |
  16. +-------+
  17. | user1 |
  18. | user3 |
  19. +-------+
  20. 2 rows IN SET (0.01 sec)
  21.  
  22.  
  23. mysql> SELECT DISTINCT a.uname FROM authuser a,memberhist b
  24.     -> WHERE a.uname<>b.uname;
  25. +--------+
  26. | uname  |
  27. +--------+
  28. | user12 |
  29. | user2  |
  30. | user3  |
  31. | user1  |
  32. +--------+
  33. 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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2005
Posts: 71
Reputation: Kate Albany is an unknown quantity at this point 
Solved Threads: 1
Kate Albany Kate Albany is offline Offline
Junior Poster in Training

Re: Question about SELECT statement

 
0
  #8
Oct 1st, 2005
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) 
  1. Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
  2. With the Partitioning, OLAP AND Oracle Data Mining options
  3. JServer Release 9.2.0.1.0 - Production
  4.  
  5. SQL> SET feedback on;
  6. SQL> SELECT * FROM users1;
  7.  
  8. USERNAME
  9. --------------------------------------------------
  10. user1
  11. user2
  12. user3
  13. user4
  14. user5
  15. user6
  16.  
  17. 6 rows selected.
  18.  
  19. SQL> SELECT * FROM users2;
  20.  
  21. USERNAME
  22. --------------------------------------------------
  23. user3
  24. user4
  25. user6
  26.  
  27. 3 rows selected.
  28.  
  29. SQL> SELECT   DISTINCT(u1.username)
  30.   2  FROM	 users1 u1,
  31.   3		   users2 u2
  32.   4  WHERE	u1.username = u2.username(+)
  33.   5  AND	  u2.username IS NULL;
  34.  
  35. USERNAME
  36. --------------------------------------------------
  37. user1
  38. user2
  39. user5
  40.  
  41. 3 rows selected.
  42.  
  43. SQL> DELETE FROM users2 u2 WHERE u2.username = 'user3';
  44.  
  45. 1 row deleted.
  46.  
  47. SQL> SELECT   DISTINCT(u1.username)
  48.   2  FROM	 users1 u1,
  49.   3		   users2 u2
  50.   4  WHERE	u1.username = u2.username(+)
  51.   5  AND	  u2.username IS NULL;
  52.  
  53. USERNAME
  54. --------------------------------------------------
  55. user1
  56. user2
  57. user3
  58. user5
  59.  
  60. 4 rows selected.
  61.  
  62. SQL> DELETE FROM users2;
  63.  
  64. 2 rows deleted.
  65.  
  66. SQL> SELECT   DISTINCT(u1.username)
  67.   2  FROM	 users1 u1,
  68.   3		   users2 u2
  69.   4  WHERE	u1.username = u2.username(+)
  70.   5  AND	  u2.username IS NULL;
  71.  
  72. USERNAME
  73. --------------------------------------------------
  74. user1
  75. user2
  76. user3
  77. user4
  78. user5
  79. user6
  80.  
  81. 6 rows selected.
  82.  
  83. SQL> ROLLBACK;
  84.  
  85. ROLLBACK complete.
  86.  
  87. SQL> SELECT   DISTINCT(u1.username)
  88.   2  FROM	 users1 u1,
  89.   3		   users2 u2
  90.   4  WHERE	u1.username = u2.username(+)
  91.   5  AND	  u2.username IS NULL;
  92.  
  93. USERNAME
  94. --------------------------------------------------
  95. user1
  96. user2
  97. user5
  98.  
  99. 3 rows selected.
Hope that helps

Kate
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2005
Posts: 71
Reputation: Kate Albany is an unknown quantity at this point 
Solved Threads: 1
Kate Albany Kate Albany is offline Offline
Junior Poster in Training

Re: Question about SELECT statement

 
0
  #9
Oct 1st, 2005
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
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MySQL Forum


Views: 4102 | Replies: 8
Thread Tools Search this Thread



Tag cloud for MySQL
"use" 1 agplv3 alfresco amazon api artisticlicense breathalyzer camparingtocolumns changingprices cmg contentmanagement count court crm data database design developer development drupal dui ec2 eliminate email enter enterprise error eudora facebook form foss gartner gnu government gpl greenit groklaw groupware images innerjoins insert ip joebrockmeier join journalism keywords laptop law legal license licensing linux maintenance managing matchingcolumns metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla multiple music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opengovernment opensource operand oracle pdf penelope php priceupdating referencedesign remove reorderingcolumns resultset saas select sharepoint sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency update virtualization
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC