MS SQL dealing with duplicate columns in rows?
This is a discussion on MS SQL dealing with duplicate columns in rows? within the SQL Server forums, part of the Microsoft SQL Server category; --> Hello, Suppose I have the following table... name employeeId email -------------------------------------------- Tom 12345 tom@localhost.com Hary 54321 Hary 54321 hary@localhost.com ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
03-01-2008, 03:40 PM
| ||||
| ||||
 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 Yas      |
|
03-01-2008, 03:40 PM
| |||
| |||
| Re: MS SQL dealing with duplicate columns in rows? On Aug 21, 10:04 am, Yas <yas...@gmail.com> wrote: > 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? |
|
03-01-2008, 03:40 PM
| |||
| |||
| Re: MS SQL dealing with duplicate columns in rows? On 21 Aug, 11:20, stephen <m0604...@googlemail.com> wrote: > On Aug 21, 10:04 am, Yas <yas...@gmail.com> wrote: > > > > > 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? basically 1 that doesn't have any fields missing.... cheers |
|
03-01-2008, 03:40 PM
| |||
| |||
| Re: MS SQL dealing with duplicate columns in rows? "Yas" <yasar1@gmail.com> schreef in bericht news:1187701509.682453.306820@o80g2000hse.googlegr oups.com... > On 21 Aug, 11:20, stephen <m0604...@googlemail.com> wrote: >> On Aug 21, 10:04 am, Yas <yas...@gmail.com> wrote: >> >> >> >> > 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? > > 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 ? |
|
03-01-2008, 03:40 PM
| |||
| |||
| Re: MS SQL dealing with duplicate columns in rows? "Yas" <yasar1@gmail.com> wrote in message news:1187701509.682453.306820@o80g2000hse.googlegr oups.com... > On 21 Aug, 11:20, stephen <m0604...@googlemail.com> wrote: >> On Aug 21, 10:04 am, Yas <yas...@gmail.com> wrote: >> >> >> >> > 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? > > 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/m...S,SQL.90).aspx -- |
|
03-01-2008, 03:40 PM
| |||
| |||
| Re: MS SQL dealing with duplicate columns in rows? > >> > 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? > > > > 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 |
|
03-01-2008, 03:40 PM
| ||||
| ||||
| Re: MS SQL dealing with duplicate columns in rows? >> >> 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.... |
 |
« An expression of non-boolean type specified in a context where a condition is expected, near ')'.
|
standard and easy way to do schema design »
| Thread Tools | |
| Display Modes | |
Linear Mode
|
|
All times are GMT. The time now is 07:16 AM.
