How can I use a variable as a clause in sql query?

Question

I am relatively new to sql and programming so please be patient.

I have a table where there are three columns similar to the following:

penID   inkID   color
01      2       red
02      2       red
03      2       red
04      1       blue
05      1       blue
06      3       black
07      3       purple
08     -5       yellow

there's a unique penID to every single pen, every inkID should correspond to it's correct color( every pen having inkID=2 should correspond only to red) but we have some anomalies that I need to track down. Now, imagine that I have thousands of inkIDs that aren't a continuous set of numbers(we could have 2,6,7,8,11..)

what I did was a query to get the list of all the InkIDs available, than I want to run another query against every single InkID to verify if there are distinct colors for the same inkID.

Because I have thousands, I don't know how to do that without having to manually write a thousand queries(not a chance). I was considering to use a c# framework to store the output of my first query in a list,and pass every list item in the second query as a variable but I don't know if that's even possible.

This is my second query so far:

Select distinct Color
from TableName
where InkID = 12

(let's say 12 for the sake of discussion)

any suggestion?Is there any other approach that I could use?

Thank you in advance

Answer 1

If you do

SELECT inkID, COUNT(DISTINCT color)
FROM TableName
GROUP BY inkID
HAVING COUNT(DISTINCT color) > 1;

That will return all of the InkIDs with more than one color assigned.