-6

I want to be able to write a query in C# Visual Studio 2012 in a WinForms Application that won't display matching records. Say I have a record in an Access DB that reads,

JOHN SMITH MALE   19
JANE DOE   FEMALE 19
JOHN SMITH MALE   19

And a query something like this

SELECT a.NAME FROM [NAME] a WHERE a.NAME = JOHN SMITH 
//but returns both records in the table

How would I be able to just return one single record from the table? Any help would be much appreciated.

DOK
  • 32,337
  • 7
  • 60
  • 92
Stick
  • 123
  • 2
  • 2
  • 12
  • do you have a primary key on your table? – Fabian Bigler Jul 26 '13 at 15:55
  • Try to use sub query with NOT IN statment. http://www.techonthenet.com/sql/in.php – NoWar Jul 26 '13 at 15:56
  • See this http://stackoverflow.com/questions/489258/linq-distinct-on-a-particular-property – Ehsan Jul 26 '13 at 15:57
  • No offense, Stick, but in the past week you've asked some REALLY basic SQL questions. You may want to go out and buy a "SQL For Dummies" book or something (NOT implying you're a dummy, but obviously you need a book that adresses basic SQL questions). De-duping a dataset is about as basic as it gets. – Johnny Bones Jul 26 '13 at 17:18

4 Answers4

5

You can do this directly in SQL.
Use distinct to get only records that are no duplicates

SELECT DISTINCT a.NAME 
FROM your_table a 
WHERE a.NAME = 'JOHN SMITH'

or use top to get only a certain amount or records as result

SELECT top 1 a.NAME 
FROM your_table a 
WHERE a.NAME = 'JOHN SMITH'
juergen d
  • 201,996
  • 37
  • 293
  • 362
1

If I understand correctly, you want to modify the query you supplied so that instead of returning

John Smith
John Smith

it only returns

John Smith

?
If that is the case then you want

SELECT DISTINCT a.NAME FROM [NAME] a WHERE a.NAME = "JOHN SMITH"

If this isn't what you wanted to do, then maybe you could update your question to better explain your problem?

pipTheGeek
  • 2,703
  • 17
  • 16
0

It isn't quite clear if you want to do this in C# or SQL. Juergen's answer is good.

If you're using, or at some point decide to use LINQ in C#, there's a FirstOrDefault() option when getting one row, like so:

return db.NameTable.Where(q => q.name == "John Smith").FirstOrDefault();
-3

Use Group By in your sql statement [reference removed 'cause of bad karma] or distinct [reference removed 'cause of bad karma]

mtzaldo
  • 105
  • 4
  • 2
    Disclaimer: I haven't downvoted you, but we generally consider w3schools to be [a bad resource](http://www.w3fools.com/). Additionally, better answers include code examples as well as links to external resources. – jwheron Jul 26 '13 at 16:03
  • 1
    jajaja OK. I know that a lot of people have a conflict because w3cschool are not affiliated with the w3c; no problemo... good to know that... – mtzaldo Jul 26 '13 at 16:25