Hello,

I am trying to figure out the SQL for doing some relatively simple operations on sets of records in a table but I am stuck. Consider a table with multiple rows per item, all identified by a common key.

For example:

serial model color
XX1   A     blue
XX2   A     blue
XX3   A     green
XX5   B     red
XX6   B     blue
XX1   B     blue

What I would for example want to do is:

1. Assuming that all model A rows must have the same color, find the rows which dont. (for example, XX3 is green).

2. Assuming that a given serial number can only point to a single type of model, find out the rows which that does not occur (for example XX1 points both to A and B)

These are all simple logically things to do. To abstract it, I want to know how to group things by using a single key (or combination of keys) and then compare the values of those records.

Should I use a join on the same table? should i use some sort of array or similar?

thanks for your help