Solved

mysql not in statement

Posted on 2011-03-21
12
325 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…

708 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

12 Experts available now in Live!

Get 1:1 Help Now