[Cialug] OT: SQL Help?

[jim kraai]

whups, forgot to put a smile at the end of that

i'm very rarely a complete ass


On Wed, Oct 2, 2013 at 2:29 PM, jim kraai <jimgkraai at gmail.com> wrote:

> Kenneth, please give a counter-example of how my solution does not meet
> the stated requirements with the stated data.
>
> Otherwise you're ruining my implicit lesson on effectively stating
> requirements.
>
> --jim
>
>
>
>
> On Wed, Oct 2, 2013 at 2:08 PM, Kenneth Younger <kyounger at gmail.com>wrote:
>
>> Jim's suggestion won't quite work, as you'll need some aggregation
>> function
>> for both fname and lname if you group on only streetaddress.
>>
>> Take a look at the OVER clause in various SQLs, or some have a COALESCE
>> function that you can comma delimit a field (or group of fields).
>>
>> -Kenny
>>
>>
>> On Wed, Oct 2, 2013 at 1:58 PM, jim kraai <jimgkraai at gmail.com> wrote:
>>
>> > select Fname, Lname, StreetAddress
>> > from SampleTable
>> > group by StreetAddress;
>> >
>> >
>> >
>> > On Wed, Oct 2, 2013 at 1:45 PM, Nathan C. Smith <NSmith at hhlawpc.com>
>> > wrote:
>> >
>> > >
>> > > Hello SQL Gurus.
>> > >
>> > > I am trying to do a quick and dirty operation on a mailing list
>> somebody
>> > > provided to me in Excel.  What I want to do is reduce the list so that
>> > only
>> > > one is sent to each household to save paper and postage.  The list is
>> > > something like this:
>> > > SampleTable
>> > > Fname   Lname   StreetAddress
>> > > Joe     Public          801 Towanda Drive
>> > > Mary    Public  801 Towanda Drive
>> > > Joe Jr. Public  801 Towanda Drive
>> > > Peter   Brady   1401 Decker
>> > > Mr.     Rogers  1024 bitwise Ave.
>> > > You get the idea.
>> > >
>> > > I was just trying to use the tools at hand - MS Excel and msquery.
>>  So I
>> > > set the Excel Table up as a datasource in ODBC.
>> > >
>> > > My plan was to do an outer join - using subqueries- one subquery with
>> > >
>> > > select distinct streetaddress from SampleTable
>> > > and the other with
>> > > select * from SampleTable
>> > >
>> > > With the Join on StreetAddress
>> > >
>> > > My understanding was if I used an outer join, using my table (in this
>> > > example) only 3 address records as the required table and the whole
>> > table I
>> > > would only get 3 items in my table.  The query gives results, but I
>> get
>> > the
>> > > whole table again (all 5 records) and it does not seem to be working
>> > like I
>> > > expect.
>> > >
>> > > Does anyone else have any thoughts on how I might attack this problem?
>> > >  Keep in mind there are a lot of limitations using JET and msquery.
>>  The
>> > > table does have an ID field to make each row unique.
>> > >
>> > _______________________________________________
>> > Cialug mailing list
>> > Cialug at cialug.org
>> > http://cialug.org/mailman/listinfo/cialug
>> >
>> > --
>> > Kenneth Younger III
>> > Founder, Sheer Focus Inc.
>> > e: kenny at sheerfocus.com
>> > p: (515) 367-0001
>> > t: @kenny <http://twitter.com/kenny>
>> > <http://cialug.org/mailman/listinfo/cialug>
>> >
>> _______________________________________________
>> Cialug mailing list
>> Cialug at cialug.org
>> http://cialug.org/mailman/listinfo/cialug
>>
>
>