Help with a SQL query design

[LittleSmudge]

I'm struggling with this and I hope a fresh pair of eyes might help.

I have two tables

tblSite
SiteId
Addr1
Addr2
Prom
( and other fields )


tblJob
JobId
SiteRef This is FK to tblSite
DoneDate
JobType
( and other fields )

Relationship is classic One to Many
There may be NONE, one or many Jobs per site.


I need to create a query that outputs

SiteId, Addr1, Prom, CurrentJobs, ContExists

SiteId, addr1 and Prom are easy from tblSite

CurrentJobs is a Number
SELECT Count(JobId) As CurrentJobs
FROM tblSite LEFT JOIN tblJob
ON tblSite.SiteId = tblJob.SiteRef
WHERE IsNull([DoneDate])

ContExists is Boolean
SELECT (Not IsNull([DoneDate])) And ([JobType]=1 Or [JobType]=13) AS LeaseExists
FROM tblSite LEFT JOIN tblJob
ON tblSite.SiteId = tblJob.SiteRef


I can get these to work individually but I can't seem to find the way to combine them into a single SQL string.


What I'm after will look like

SiteId,     Addr1,     Prom, CurrentJobs, ContExists
    1   1 The Road      -1         0            0
    2   No 3 Hi St.     -1         0           -1
    3   The House       -1         2            0
    4   Ivy Cottage     -1         1            0
    5   Old Mill        -1         0           -1

etc. ..


Any ideas ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

I can get these to work individually
So you may try this:
SELECT S.SiteId, S.Addr1, S.Prom
,(SELECT Count(JobId) FROM tblJob J
WHERE S.SiteId = J.SiteRef And IsNull(DoneDate)
) As CurrentJobs
,(SELECT (Not IsNull(DoneDate)) And (JobType=1 Or JobType=13)
FROM tblJob J WHERE S.SiteId = J.SiteRef
) AS ContExists
FROM tblSite S
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244