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. > > > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
