Re: SELECT DISTINCT records based only on two columns

Arnie,

You make some good points about the table design. Also, thanks for shedding
a bit more light on how the DISTINCT keyword works. Based on the query you
wrote, I see my mistake was to include the UID field in my query. I don't
think I will actually need that field so that should do the the trick. But
out of curiosity, I wonder how the query could be written if I did need the
UID field. Would I use multiple select statements and the IN keyword or
somthing like that?





"Arnie Rowland" wrote:

First, a suggestion.

Make the ZipCode field a varchar() -either 9 or 10, depending upon what you do with the dash. If you use an int, leading zeros (upper Northeast States) is lost, and 9 digit zipcodes (e.g., 98765-4321) will do the math and store the result.

Similar leading zero problem with SSN

The use of DISTINCT serves two purposes. First, it makes sure that there are no duplicates by testing all of the selected columns as a unit -ignoring capitalization differences (with the standard SQL_Latin1_General_CP1_CI_AS collation). Second, it provides a 'sorted' output since it has to accomplish an ORDER BY just to remove duplicates. However, if there is any character, including space, period, etc., difference, the comparison fails.

CREATE TABLE dbo.#MY_CONTACTS_TBL
( UID smallint IDENTITY(1,1) NOT NULL
, SSN char(9) NULL
, FName varchar(50)
, LName varchar(50)
, Address1 varchar(50)
, Address2 varchar(50)
, City varchar(50)
, State varchar(50)
, Zipcode varchar(10) NULL
)
GO

INSERT INTO #MY_CONTACTS_TBL VALUES ( '123456789','Marc','Archuleta','123 Sesame St','apartment 1','Columbus','OH','43215' )
INSERT INTO #MY_CONTACTS_TBL VALUES ( '234567890','John','Doe','123 Dough St',NULL,'Columbus','OH','43215' )
INSERT INTO #MY_CONTACTS_TBL VALUES ( '345678901','John2','Doe','456 Dough St',NULL,'Columbus','OH','43215' )
INSERT INTO #MY_CONTACTS_TBL VALUES ( '456789012','John','DoeTwo','789 Dough St',NULL,'Columbus','OH','43215' )
INSERT INTO #MY_CONTACTS_TBL VALUES ( '234567890','john','doe','123 dough St',NULL,'columbus','oh','43215' )
INSERT INTO #MY_CONTACTS_TBL VALUES ( '234567890','John','Doe','123 Pumpernickel Ave',NULL,'New York','NY','10016' )
INSERT INTO #MY_CONTACTS_TBL VALUES ( '234567890','John','Doe','123 Dough St.',NULL,'Columbus','OH','43215' )

SELECT DISTINCT
FName + ' ' + LName
, Address1
, isnull( Address2, '' )
, City + ', ' + State + ' ' + ZipCode
FROM #MY_CONTACTS_TBL

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"archuleta37" <archuleta37@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:269374D2-A26E-42B4-8CA1-0BF057600B39@xxxxxxxxxxxxxxxx
I'm working on a mailing list and want to select records where the both the
SSN number and Address fields are unique, since I may want to send a piece of
mail to more than one address for a person, but not multiple pieces to the
same address for that person. I've created a test table and some test data to
try to get my syntax right (below) but so far have had no luck.

I'm not certain if I can do this using DISTINCT or the UNIQUE keyword, nor
how to get my syntax right. Could someone help me with this?

Thanks in advance :-)
Marc


************* CREATE TABLE *****************************
USE [Northwind]
GO
/****** Object: Table [dbo].[MY_CONTACTS_TBL] Script Date: 08/17/2006
09:07:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MY_CONTACTS_TBL](
[UID] [smallint] IDENTITY(1,1) NOT NULL,
[SSN] [bigint] NULL,
[FNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LNAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDRESS1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDRESS2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CITY] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ZIP] [int] NULL,
CONSTRAINT [PK_MY_CONTACTS_TBL] PRIMARY KEY CLUSTERED
(
[UID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

******************* TABLE DATA *****************************
UID,SSN,FNAME,LNAME,ADDRESS1,ADDRESS2,CITY,STATE,ZIP
1,123456789,Marc,Archuleta,123 Sesame St,apartment 1,Columbus,OH,43215
2,234567890,John,Doe,123 Dough St,,Columbus,OH,43215
3,345678901,John2,Doe,456 Dough St,,Columbus,OH,43215
4,456789012,John,DoeTwo,789 Dough St,,Columbus,OH,43215
5,234567890,John,Doe,123 Dough St,,Columbus,OH,43215
6,234567890,John,Doe,123 Pumpernickel Ave,,New York,NY,10016

.

Relevant Pages

  • Re: Help needed on creating a subform
    ... Don't enter anything in this procedure yet but scroll down below the End Sub ... "ORDER BY QuoteId DESC, Keyword" ... based on the current value of the quote id. ... A UNION query concatenates the results of two separate queries. ...
    (microsoft.public.access.forms)
  • Re: Creatinig a database to update in alphabetical order
    ... You can then create a Query which takes all of the records in the ... Some attributes of a movie would be the Title, the studio, ... MovieID Autonumber Primary Key ' links tables together ... Keyword ...
    (microsoft.public.access.gettingstarted)
  • RE: Joining tables based on strings contained within field
    ... Open up a new query based on the Main table. ... paste in the SQL statement below after removing what already there. ... Jerry Whittle, Microsoft Access MVP ... the Keyword table showing only the Key column? ...
    (microsoft.public.access.queries)
  • Re: Keywords in bibliography database
    ... The concept of the UNION query it is to build a SELECT for each keyword ... SELECT ArticleID, Keyword FROM tblArticleKeyword WHERE Keyword = 'fish' ...
    (comp.databases.ms-access)
  • Re: Error when calling the OleDbDataAdapter.Fill() method
    ... Have you tried copying and pasting the query into an Access query ... it is caused by using a keyword as a fieldname in my database. ... Here is the SQL statement I am executing (I am using Microsoft Access as my ... SELECT headline,url,summary,updated FROM worldnews EXCEPT (SELECT ...
    (microsoft.public.dotnet.framework.adonet)