[Cialug] OT: SQL Help?

[jim kraai]

Ken,

You are correct in every way.

I didn't know about sql fiddle--that's great.

--jim



On Wed, Oct 2, 2013 at 3:22 PM, Kenneth Younger <kyounger at gmail.com> wrote:

> I'll admit my response was based on experience, but perhaps not a full
> experience. I know this doesn't work in Access, SQL Server, and Oracle. But
> I wasn't sure about other DBs. :)
>
> Here's Access: http://imgur.com/02fhIxG
> And here's a SQL Fiddle with our example, this will let you test several
> other DBs: http://sqlfiddle.com/#!1/00e9d/1
>
> MySQL doesn't require the aggregation function (which I think should be
> forced as explicit). SQLite is the only other DB on Fiddle that this works
> with implicitly.
>
> If he's working in the MS world, then I'm guessing being implicit might not
> work.
>
> -Kenny
>
>
> On Wed, Oct 2, 2013 at 2:30 PM, jim kraai <jimgkraai at gmail.com> wrote:
>
> > 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
> > >>
> > >
> > >
> > _______________________________________________
> > 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
>