[Cialug] OT: SQL Help?

Thu Oct 3 13:03:51 CDT 2013

Thanks for the ideas gents.  SQL fiddle is cool, thank you for that tidbit too.

-Nate

-----Original Message-----
From: cialug-bounces at cialug.org [mailto:cialug-bounces at cialug.org] On Behalf Of jim kraai
Sent: Wednesday, October 02, 2013 3:55 PM
To: Central Iowa Linux Users Group
Subject: Re: [Cialug] OT: SQL Help?

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
>
_______________________________________________
Cialug mailing list
Cialug at cialug.org
http://cialug.org/mailman/listinfo/cialug

________________________________

[http://owa.hhlawpc.com/graphics/HHLogo.jpg]<http://www.hhlawpc.com>
This E-mail (including any attachments) is covered by the Electronic Communications Privacy Act, 18 U.S.C. §§ 2510-2521, is confidential and may contain attorney-client materials and/or attorney work product, legally privileged and protected from disclosure.  If you are not the intended recipient, you are hereby notified that any retention, dissemination, distribution, or copying of this communication is strictly prohibited.  Please respond to the sender that you have received the message in error, then delete it and destroy any and all copies of it.  If you are a client of our firm, this e-mail confirms that communication to you by e-mail is an acceptable way to transmit attorney-client information.  Thank you.