Solved

mysql not in statement

Posted on 2011-03-21
12
332 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
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.

 
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
 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Powershell script 13 104
DBF to ... Converter 5 72
append to an ms access field 6 35
FrontEnd tools to create web database application 7 62
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…

825 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