MS SQL dealing with duplicate columns in rows?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Yas
    #1

    MS SQL dealing with duplicate columns in rows?

    Hello,

    Suppose I have the following table...

    name employeeId email
    --------------------------------------------
    Tom 12345 tom@localhost.c om
    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

    Tags: None
    • stephen
      #2
      Re: MS SQL dealing with duplicate columns in rows?

      On Aug 21, 10:04 am, Yas <yas...@gmail.c omwrote:
      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?

        Comment

        • Yas
          #3
          Re: MS SQL dealing with duplicate columns in rows?

          On 21 Aug, 11:20, stephen <m0604...@googl email.comwrote:
          On Aug 21, 10:04 am, Yas <yas...@gmail.c omwrote:
          >
          >
          >
          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

            Comment

            • Luuk
              #4
              Re: MS SQL dealing with duplicate columns in rows?


              "Yas" <yasar1@gmail.c omschreef in bericht
              news:1187701509 .682453.306820@ o80g2000hse.goo glegroups.com.. .
              On 21 Aug, 11:20, stephen <m0604...@googl email.comwrote:
              >On Aug 21, 10:04 am, Yas <yas...@gmail.c omwrote:
              >>
              >>
              >>
              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 ?



                Comment

                • David Portas
                  #5
                  Re: MS SQL dealing with duplicate columns in rows?

                  "Yas" <yasar1@gmail.c omwrote in message
                  news:1187701509 .682453.306820@ o80g2000hse.goo glegroups.com.. .
                  On 21 Aug, 11:20, stephen <m0604...@googl email.comwrote:
                  >On Aug 21, 10:04 am, Yas <yas...@gmail.c omwrote:
                  >>
                  >>
                  >>
                  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:
                  Books Online
                  http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

                  --


                    Comment

                    • Martijn Tonies
                      #6
                      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
                      Upscene: Database tools for Developers. From database design, to implementation, documentation and testing.
                      http://www.upscene.com
                      Upscene: database tools for Oracle, PostgreSQL, InterBase, Firebird, SQL Server, MySQL, MariaDB, NexusDB, SQLite. Home of Database Workbench, Advanced Data Generator and Hopper.

                      My thoughts:
                      http://blog.upscene.com/martijn/

                      Database development questions? Check the forum!
                      http://www.databasedevelopmentforum.com



                        Comment

                        • Luuk
                          #7
                          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....


                            Comment

                            Previous template Next
                            Working...