[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

mysql not in statement

Posted on 2011-03-21
12
Medium Priority
?
352 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 500 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 500 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1000 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 1000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

656 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