MS SQL dealing with duplicate columns in rows?

Hello,

Suppose I have the following table...

name employeeId email
--------------
Tom 12345 tom@.localhost.com
Hary 54321
Hary 54321 hary@.localhost.com
I only want unique employeeIds return. If I use Distinct it will still
return all of the above as the email is different/missing. Is there a
way to query in SQL so that only distinct employeeId is returned? no
duplicates.

I wouuld like to say WHERE no blank fields are present to get the
right row to return.

Many thanks

YasOn Aug 21, 10:04 am, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
Suppose I have the following table...
>
name employeeId email
--------------
Tom 12345 t...@.localhost.com
Hary 54321
Hary 54321 h...@.localhost.com
>
I only want unique employeeIds return. If I use Distinct it will still
return all of the above as the email is different/missing. Is there a
way to query in SQL so that only distinct employeeId is returned? no
duplicates.
>
I wouuld like to say WHERE no blank fields are present to get the
right row to return.
>
Many thanks
>
Yas

Which row for Hary do you want to be returned? The one without an
email address or the one with the email address?|||On 21 Aug, 11:20, stephen <m0604...@.googlemail.comwrote:

Quote:

Originally Posted by

On Aug 21, 10:04 am, Yas <yas...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

Hello,

>

Quote:

Originally Posted by

Suppose I have the following table...

>

Quote:

Originally Posted by

name employeeId email
--------------
Tom 12345 t...@.localhost.com
Hary 54321
Hary 54321 h...@.localhost.com

>

Quote:

Originally Posted by

I only want unique employeeIds return. If I use Distinct it will still
return all of the above as the email is different/missing. Is there a
way to query in SQL so that only distinct employeeId is returned? no
duplicates.

>

Quote:

Originally Posted by

I wouuld like to say WHERE no blank fields are present to get the
right row to return.

>

Quote:

Originally Posted by

Many thanks

>

Quote:

Originally Posted by

Yas

>
Which row for Hary do you want to be returned? The one without an
email address or the one with the email address?

basically 1 that doesn't have any fields missing...

cheers|||"Yas" <yasar1@.gmail.comschreef in bericht
news:1187701509.682453.306820@.o80g2000hse.googlegr oups.com...

Quote:

Originally Posted by

On 21 Aug, 11:20, stephen <m0604...@.googlemail.comwrote:

Quote:

Originally Posted by

>On Aug 21, 10:04 am, Yas <yas...@.gmail.comwrote:
>>
>>
>>

Quote:

Originally Posted by

Hello,

>>

Quote:

Originally Posted by

Suppose I have the following table...

>>

Quote:

Originally Posted by

name employeeId email
--------------
Tom 12345 t...@.localhost.com
Hary 54321
Hary 54321 h...@.localhost.com

>>

Quote:

Originally Posted by

I only want unique employeeIds return. If I use Distinct it will still
return all of the above as the email is different/missing. Is there a
way to query in SQL so that only distinct employeeId is returned? no
duplicates.

>>

Quote:

Originally Posted by

I wouuld like to say WHERE no blank fields are present to get the
right row to return.

>>

Quote:

Originally Posted by

Many thanks

>>

Quote:

Originally Posted by

Yas

>>
>Which row for Hary do you want to be returned? The one without an
>email address or the one with the email address?

>
basically 1 that doesn't have any fields missing...
>
cheers
>

SELECT * from table where name<>"" and employeeId<>0 and email<>"";

but why would you have this second row "Hary 54321 " in your
table anyway?

would it not be better to create unique index on emplyeeId ?|||"Yas" <yasar1@.gmail.comwrote in message
news:1187701509.682453.306820@.o80g2000hse.googlegr oups.com...

Quote:

Originally Posted by

On 21 Aug, 11:20, stephen <m0604...@.googlemail.comwrote:

Quote:

Originally Posted by

>On Aug 21, 10:04 am, Yas <yas...@.gmail.comwrote:
>>
>>
>>

Quote:

Originally Posted by

Hello,

>>

Quote:

Originally Posted by

Suppose I have the following table...

>>

Quote:

Originally Posted by

name employeeId email
--------------
Tom 12345 t...@.localhost.com
Hary 54321
Hary 54321 h...@.localhost.com

>>

Quote:

Originally Posted by

I only want unique employeeIds return. If I use Distinct it will still
return all of the above as the email is different/missing. Is there a
way to query in SQL so that only distinct employeeId is returned? no
duplicates.

>>

Quote:

Originally Posted by

I wouuld like to say WHERE no blank fields are present to get the
right row to return.

>>

Quote:

Originally Posted by

Many thanks

>>

Quote:

Originally Posted by

Yas

>>
>Which row for Hary do you want to be returned? The one without an
>email address or the one with the email address?

>
basically 1 that doesn't have any fields missing...
>
cheers
>

What is the key of your table? If you don't have a key then you need to fix
the design before you can expect a reasonable solution in SQL.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Hello,
>
Suppose I have the following table...
>
name employeeId email
--------------
Tom 12345 t...@.localhost.com
Hary 54321
Hary 54321 h...@.localhost.com
>
I only want unique employeeIds return. If I use Distinct it will

still

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

return all of the above as the email is different/missing. Is there a
way to query in SQL so that only distinct employeeId is returned? no
duplicates.
>
I wouuld like to say WHERE no blank fields are present to get the
right row to return.
>
Many thanks
>
Yas
>
Which row for Hary do you want to be returned? The one without an
email address or the one with the email address?

basically 1 that doesn't have any fields missing...

cheers

>
SELECT * from table where name<>"" and employeeId<>0 and email<>"";

Just a note:

Please, no double quotes for string constants, use single quotes.

Double quotes are reserved for "delimited identifiers" as defined by the
SQL Standard and supported by MS SQL Server.

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||>>

Quote:

Originally Posted by

Quote:

Originally Posted by

>SELECT * from table where name<>"" and employeeId<>0 and email<>"";

>
Just a note:
>
Please, no double quotes for string constants, use single quotes.
>
Double quotes are reserved for "delimited identifiers" as defined by the
SQL Standard and supported by MS SQL Server.
>

I tend to forget those things, as its different in every programming
evironment i use...
some of the don't care, some use double quotes, and some use single
quotes...

thanks anyway for this reminder...