Solved

mysql not in statement

Posted on 2011-03-21
12
336 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…

734 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