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

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

Collectives™ on Stack Overflow

php mysql nested query "where not in"

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related