CODEWITHSUNDEEP
c#sqlms-accessoledbdistinct
hex c
hex c

Reputation: 745

SQL - select distinct across multiple columns

I'm having trouble writing the SQL code to select distinct contacts across 8 columns from an access table. I want to only get the distinct results to not have duplicates. Below is what I've tried.

cmd.CommandText = "select distinct c1, c2, c3, c4, c5, c6, c7, c8 
                   from table 
                   where state IN (" + paramClause.ToString() + ")";

My purpose is to show this on a label with no duplicates.

Upvotes: 0

Views: 948

Answers (2)

Johnny Bones
Johnny Bones

Reputation: 8402

You have:

cmd.CommandText = "select distinct c1, c2, c3, c4, c5, c6, c7, c8 
                   from table 
                   where state IN (" + paramClause.ToString() + ")";

But you need to wrap strings in single quotes. I think the problem is in your WHERE clause. Try doing this:

cmd.CommandText = "select distinct c1, c2, c3, c4, c5, c6, c7, c8 
                   from table 
                   where state IN ('" + paramClause.ToString() + "')";

Upvotes: 0

Corrado Piola
Corrado Piola

Reputation: 869

If I have correctly understood, you have contacts in one or more column (from c1 to c8).

If so, try to rewrite your SQL statement like the following:

SELECT C1
FROM TABLE
WHERE STATE IN (...)
UNION
SELECT C2
FROM TABLE
WHERE STATE IN (...)
...
UNION
SELECT C8
FROM TABLE
WHERE STATE IN (...)

The UNION operator eliminates duplicates by itself.

I hope this helps you....

Upvotes: 7

Related Questions