You need to do a left join. If this was interbase (or Ansi) I would do the
following:
select a.CrossID, a.Name, a.LandScape, count(b.CrossID) as CountFld
from ctabhed a left join ctabdat b on a.CrossID = b.CrossID
group by a.CrossID, a.Name, a.LandScape
"Derricutt, Mark" <[EMAIL PROTECTED]> on 17/12/98 10:41:49
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
cc: (bcc: Peter Jones/Logistics&Information
Technology/Christchurch/Foodstuffs)
Subject: [DUG]: SQL Problem..... urgent answer required...
Hi, I have the following query:
select a.CrossID, a.Name, a.LandScape, count(b.CrossID) as CountFld
from ctabhed a, ctabdat b
where a.CrossID = b.CrossID
group by a.CrossID, a.Name, a.LandScape
Which works perfect, apart from one flaw... when there is no matching
details records in table b, the query doesn't return a record to the
dataset
with CountFld = 0 as I want.
Does anyone have any ideas??? This is a kinda urgent dead-line stopping
problem and I'm stumped on this :(
Thanks in advance,
Mark
--
Mark Derricutt, Software Engineer
PB Power (NZ) Ltd
Now Playing... <no audio cd present>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz