0

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

Ylenia88m
  • 83
  • 6
  • why do you think that you need to write thousands of query's? I would suggest doing a google search on Basic SQL Tutorial and how to construct what we call `Parameterized Query` tons of examples online as well for that too.. – MethodMan Jan 09 '17 at 15:22
  • It would be great if you include your works to this target – sujith karivelil Jan 09 '17 at 15:24
  • 1
    Something like `select inkid, count(distinct color) from table group by inkid` should give you a list of the ink ids and how many distinct colors each one has. If you actually want the colors just do `select distinct inkid, color from table` – juharr Jan 09 '17 at 15:25
  • It is unquestionable that I will study more, thank you for your answers, that is much appreciated – Ylenia88m Jan 09 '17 at 15:41

1 Answers1

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.

JCollerton
  • 3,227
  • 2
  • 20
  • 25