mysql not in statement

select actives.* from actives where active_id not in (select active_id from conditions_actives_score)

while the above are correct, they would work slowly if the second table is large

ah sorry forgot to add:

table actives contained 5 and the second table contained 350.000 rows

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);

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 ?

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;

Select allOpen 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);

Select allOpen 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;

Select allOpen 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)');

Select allOpen in new window

Thank you, mostart. =)

Thank you. =)