php mysql nested query "where not in"

Question

i have two tables test_category and results:

CREATE TABLE IF NOT EXISTS `test_category` (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `score_type` varchar(50) NOT NULL,
  `sub_code` int(11) NOT NULL,
  `duration` varchar(10) NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `sub_code` (`sub_code`)
)

CREATE TABLE IF NOT EXISTS `results` (
  `res_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `score_type` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  `date_taken` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`res_id`),
  KEY `user_id` (`user_id`),
  KEY `score_type` (`score_type`)
)

i want to select some columns that exist on test_category but doesn't exist in results.

here is the sql i've tried but it does not work:

select _id, score_type from test_category where _id in ('13') and not in(select score_type from results where user_id=349);

@BrokenHeartღ You're right. The same name but different types ... — valex
– valex, Commented May 29, 2014 at 6:06
@valex that's why I have not mapped these columns for NOT IN. — Code Lღver
– Code Lღver, Commented May 29, 2014 at 6:13

ravikumar · Accepted Answer · 2014-05-29 05:49:35Z

2

You missed to mention the column name after AND

Try this,

SELECT _id, score_type FROM test_category WHERE _id IN
('13') AND _id NOT IN(SELECT score_type FROM results WHERE user_id=349);

answered May 29, 2014 at 5:49

ravikumar

9031 gold badge8 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christian Burgos Over a year ago

i see, so that's where i was wrong. your sql command works. thanks!

Code Lღver · Accepted Answer · 2014-05-29 05:49:40Z

1

You query should be this:

select _id, score_type 
    FROM test_category 
      WHERE _id in ('13') 
       AND _id NOT IN (select score_type 
                         FROM results 
                           WHERE user_id=349);

answered May 29, 2014 at 5:49

Code Lღver

15.6k16 gold badges61 silver badges75 bronze badges

Collectives™ on Stack Overflow

php mysql nested query "where not in"

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related