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. > ______________________________________________________________________ Macromedia ColdFusion 5 Training from the Source Step by Step ColdFusion http://www.amazon.com/exec/obidos/ASIN/0201758474/houseoffusion Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
