Solved

mysql not in statement

Posted on 2011-03-21
12
327 Views
Last Modified: 2012-05-11
Hi,

I have these two tables:
CREATE TABLE conditions_actives_score(
  cas_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT(11) UNSIGNED DEFAULT NULL,
  condition_id INT(11) UNSIGNED DEFAULT NULL,
  active_id INT(11) UNSIGNED DEFAULT NULL,
  safe SMALLINT(6) DEFAULT NULL,
  effectiveness SMALLINT(6) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (cas_id)
)

Open in new window


CREATE TABLE actives(
  active_id INT(11) NOT NULL AUTO_INCREMENT,
  active_name VARCHAR(100) NOT NULL,
  PRIMARY KEY (active_id)
)

Open in new window


I want to find all the active_name(s) from table actives where actives.active_id IS NOT FOUND IN conditions_actives_score.active_id.

I tried this:
select actives.* from actives where conditions_actives_score.active_id not in actives.active_id

Open in new window


but it gives me an error.
0
Comment
Question by:Victor Kimura
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 12

Accepted Solution

by:
enachemc earned 125 total points
ID: 35180876
select actives.* from actives left join conditions_actives_score on conditions_actives_score.active_id = actives.active_id
where conditions_actives_score.active_id is null
0
 
LVL 8

Expert Comment

by:mostart
ID: 35180884
select actives.* from actives where active_id not in (select active_id from conditions_actives_score)
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 125 total points
ID: 35181030
use not exists

select *
from actives as a
where not exists (select active_id from conditions_actives as x
where x.active_id = a.active_id)
0
 
LVL 12

Expert Comment

by:enachemc
ID: 35181096
while the above are correct, they would work slowly if the second table is large
0
 
LVL 8

Assisted Solution

by:mostart
mostart earned 250 total points
ID: 35182406
can't really confirm this:

mysql> select SQL_NO_CACHE actives.* from actives left join conditions_actives_score on conditions_actives_score.active_id = actives.active_id where conditions_actives_score.active_id is null;
+-----------+---------------+
| active_id | active_name   |
+-----------+---------------+
|     49999 | 'myName49999' |
+-----------+---------------+
1 row in set (0.97 sec)

mysql> select SQL_NO_CACHE actives.* from actives where active_id not in (select active_id from conditions_actives_score);
+-----------+---------------+
| active_id | active_name   |
+-----------+---------------+
|     49999 | 'myName49999' |
+-----------+---------------+
1 row in set (0.15 sec)


I ran the two versions several times an always had the second one around 4 times faster..



0
 
LVL 8

Expert Comment

by:mostart
ID: 35182412
ah sorry forgot to add:

table actives contained 5 and the second table contained 350.000 rows
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Victor Kimura
ID: 35189298
Hi mostart,

This works for me:
select SQL_NO_CACHE actives.* from actives left join conditions_actives_score on conditions_actives_score.active_id = actives.active_id where conditions_actives_score.active_id is null;

But for some reason nothing is returned on this second sql:
select SQL_NO_CACHE actives.* from actives where active_id not in (select active_id from conditions_actives_score);

0
 
LVL 8

Expert Comment

by:mostart
ID: 35191126
For me both are returning the same result as already shown above.
Could you post the corresponding lines from your mysql console containing query and results ?
0
 

Author Comment

by:Victor Kimura
ID: 35207908
Hi mostart:

No errors. Just blank. I see the active_id, active_name columns.

CREATE TABLE skinca_dermapproved.conditions_actives_score(
  cas_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT(11) UNSIGNED DEFAULT NULL,
  condition_id INT(11) UNSIGNED DEFAULT NULL,
  active_id INT(11) UNSIGNED DEFAULT NULL,
  safe SMALLINT(6) DEFAULT NULL,
  effectiveness SMALLINT(6) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (cas_id)
)
ENGINE = MYISAM
AUTO_INCREMENT = 19
AVG_ROW_LENGTH = 21
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

Open in new window


INSERT INTO conditions_actives_score VALUES 
  (1, 1, 1, 5, 0, 0),
  (2, 1, 1, 8, 1, 1),
  (3, 1, 1, 6, 1, 1),
  (4, NULL, NULL, NULL, NULL, NULL),
  (5, 1, 1, 7, 0, NULL),
  (6, 1, 1, 9, 1, NULL),
  (7, 1, 2, 1, 1, NULL),
  (8, 1, 2, 2, 0, NULL),
  (9, 1, 1, 10, 1, NULL),
  (10, 1, 1, 11, 1, NULL),
  (11, 2, 1, 5, 1, NULL),
  (12, 2, 1, 6, 1, NULL),
  (13, 2, 1, 7, 0, NULL),
  (14, 2, 2, 1, 0, NULL),
  (15, 2, 2, 2, 1, NULL),
  (16, 1, 2, 3, 1, NULL),
  (17, 1, 2, 4, 1, NULL),
  (18, 1, 2, 5, 0, NULL);

Open in new window


CREATE TABLE skinca_dermapproved.actives(
  active_id INT(11) NOT NULL AUTO_INCREMENT,
  active_name VARCHAR(100) NOT NULL,
  PRIMARY KEY (active_id)
)
ENGINE = MYISAM
AUTO_INCREMENT = 27
AVG_ROW_LENGTH = 26
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

Open in new window


INSERT INTO actives VALUES 
  (1, 'Hydroquinone'),
  (2, 'Arbutin'),
  (3, 'Vitamin C'),
  (4, 'Kojic acid'),
  (5, 'Benzoyl peroxide'),
  (6, 'Salicylic acid'),
  (7, 'Alpha-hydroxy acids'),
  (8, 'Triclosan'),
  (9, 'Sulfur'),
  (10, 'Polyhydroxy acids'),
  (11, 'Retinols'),
  (12, 'Almond'),
  (13, 'Almond (Prunus amygdalus)'),
  (14, 'Citric Acid (Alpha-Hydroxy Acid)');

Open in new window

0
 
LVL 8

Assisted Solution

by:mostart
mostart earned 250 total points
ID: 35214014
Ok I see you have NULL values in the active_id column of the onditions_actives_score table. Thats why you don't see results.

Change the query like this:

select actives.* from actives where active_id not in (select active_id from conditions_actives_score where active_id is not null);
0
 

Author Comment

by:Victor Kimura
ID: 35237291
Thank you, mostart. =)
0
 

Author Closing Comment

by:Victor Kimura
ID: 35237304
Thank you. =)
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now