Mysql: How do I write select where ids are not in another table

Question

 I have a simple structure of 2 tables: contacts and group_contacts. A contact can belong to one, many or no groups. I'm trying to write a select statement that will give me all the contacts that don't belong to group_id '123'. The negative, don't, has me confused. CREATE TABLE IF NOT EXISTS `contacts` ( `contact_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL) CREATE TABLE IF NOT EXISTS `group_contacts` ( `contact_id` bigint(20) unsigned NOT NULL, `group_id` int(11) unsigned NOT NULL) thanks

Vanji · Accepted Answer · 2010-11-29 06:33:11Z

4

select a.contact_id from contacts a, group_contacts b where b.group_id<>123 and b.contact_id=a.contact_id;

answered Nov 29, 2010 at 6:33

Vanji

1,70414 silver badges23 bronze badges

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

2 Comments

user319198 Over a year ago

joining condition should be always in form clause to filter tables multiplications.

Vanji Over a year ago

i am unable to understand your comment bro!!... what do you mean by form clause ??

kuriouscoder · Accepted Answer · 2010-11-29 06:32:21Z

You would have to proceed this in two macro-steps:

First you would need to left outer join between contacts and group_contacts table so that all contacts that do and don't have any relevant relation in group_contacts table is selected
Then in the where clause of group_contacts group_id of 123 should be loaded.

score 2 · Accepted Answer · 2010-11-29 06:40:37Z

select * from contacts as ct left join group_contacts as gc on ct.contact_id=gc.contact_id where gc.group_id!=123

Haim Evgi · Accepted Answer · 2010-11-29 06:35:14Z

1

try this

select * from contacts a left join group_contacts b on a.contact_id = b.contact_id where b.group_id !=123

edited Nov 29, 2010 at 6:35

answered Nov 29, 2010 at 6:29

Haim Evgi

126k46 gold badges222 silver badges226 bronze badges

2 Comments

user319198 Over a year ago

syntax error in query. please check.I am not sure we can use like "left join b group_contacts ".Alias name should be after table name

Haim Evgi Over a year ago

i fix thank you, it typo mistake b need to be after the table name ofcurse

Collectives™ on Stack Overflow

Mysql: How do I write select where ids are not in another table

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Related