Great observation Kay, that you sense a need to move on to better SQL. No question, that's a valuable next step that many never take. Outer joins, distincts, groups, aggregate functions, and more all await the curious developer, and they're not too difficult with the right books. Again, just as their are good design books, there are also good SQL books (usually not the same, though sometimes a book covers both design and SQL).
Perhaps the best, in terms of ease to digest, is Ben Forta's Teach Yourself SQL in 10 Minutes. Many scoff at such a title, but he really did a great job, especially in this aspect of taking folks into some depth that they might not get with just doing simple Select, insert, update, and delete. It's around $10 and just a couple hundred pages. Great value. Of course, Celko's SQL For Smarties is a real brain burner, but perhaps too much as a "next step" for you. A gentler intro is Judith Bowman's Practical SQL Handbook, which recently underwent a revision. Another is SQL for Mere Mortals. There are others. Just thought I'd share. /charlie -----Original Message----- From: Kay Smoljak [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 11:58 PM To: SQL Subject: RE: joins Thanks Matthew, Your suggestions work great. I'll need to brush up on joins I think - I'm coming across this stuff more and more, and simple SQL isn't cutting it anymore! Kay. > -----Original Message----- > From: Matthew Todd [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, 16 January 2002 3:33 AM > To: SQL > Subject: Re: joins > > > Kay, > > To get you started you first need to use an outerjoin. > > select distinct clients.ID, clients.name, support.dated > from clients, support > where support.clientID =* clients.ID > order by support.dated, clients.name > > This will get you all of the clients even if they do not have > a support call > logged against them. > > Your query is returning distinct records. The reason that > your getting each > instance of a call has to do with the date, it is always > different therefore > it is distinct. Take off the support.dated and see what you get back. > > That date field will cause you some problems if you keep it > in the query > like it is. You probably need to use an aggregate function to > get what you > want. Give this a try: > > select clients.ID, clients.name, max(support.dated) > from clients, support > where support.clientID =* clients.ID > group by clients.id, clients.name > order by max(support.dated) desc, clients.name > > This should give you what you want. > > Let me know if it works out. > > Matthew > > > > >From: "Kay Smoljak" <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: SQL <[EMAIL PROTECTED]> > >Subject: joins > >Date: Wed, 16 Jan 2002 11:16:14 +0800 > > > >Hi all, > > > >I have a funny requirement... I think it involves some > specific type of > >join, but I'm not sure which type. I have a clients table, and a > >support incidents table that uses the clients table primary key as a > >foreign key. I want to select all the clients for a drop > down box, but > >have the 10 that have most recently had a support record > logged appear > >at the top. To complicate matters further, not all client > records will > >have support incidents logged against them, but I need all of the > >client to appear in the recordset. > > > >Disregarding the top 10 requirement until later, I have this so far: > > > >select distinct clients.ID, clients.name, support.dated > >from clients, support > >where support.clientID = clients.ID > >order by support.dated, clients.name > > > >But this is not selecting distinct records - If a certain client has > >had five support incidents, that client will appear five times. It's > >also not showing clients that don't have any support > incidents logged. > > > >Argh.... Can anyone point me in the right direction? SQL > Server 2K by > >the way. > > > >Thanks, > >Kay. > > > ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
