select id and check if no more of that id is found on the same table

[nvso]

There is possibly a simple way for this but english not being my native language I cant come up with proper search terms for such need.. That is:

 

I have to tables, products and products_groups

 

products              products_groups

products_id        products_id      group_id

 

1                          1                    10

                            1                    20

                            1                    30

2                          2                    20

 

In this example, a certain products with ID 1 belongs to many different groups (10,20 and 30). Now, I wanted to remove a certain group, say, group "20". I also need to SELECT a products data if it belongs to group "20".

 

In this case, product with ID "2" is to be selected from products_table, but since product ID "1" is still attached to two other groups (10 and 20) I do not want to select that products ID. In my query, I can only know the ID of the group.

 

So, in other words, I need something like "select products_id from table_products WHERE products_groups.products returns no more than one row with selected categories product_id"

 

Hope you understand what I'm after. I know how to do this "the hard way" but if there was a single query I'd be delighted

 

Thanks in advance!

 

- M

[cags]

I'm not certain, but something like this may work (note, you should only ever test something like this on data you don't mind losing). I'm sure fenway/kickstart or somebody else will soon reply if I'm too far off base.

 

DELETE FROM products_groups WHERE group_id = 20
DELETE FROM products WHERE products_id NOT IN (SELECT DISTINCT products_id FROM products_groups)

[fenway]

I'm not sure I follow.. you don't want to select them, or you want to delete them?

[nvso]

thanks, that worked just fine for me!

 

fenway:  i wanted to select products id from products_table, if it did not occur anymore on products_groups -table, after deleting a certain group id (attached to a product_id).. Gah, never mind lol

[cags]

The way you describe it sounds like you wish the second line of my code to be a SELECT statement to simply list the products_id's that no longer have an entry in products_groups. Obviously the code I provided will remove them.

[emopoops]

the problem whith that cags user is statin... its no sea approved.