Hi
I am slowly exploring using SQL to assist my Access front end better than in the past. I moved the backend to SQL about 10 years ago but didn't do much in the way of mods or integration for many years. Now I am undertaking a complete rewrite to update a lot of the tools and using SP to help with speed etc. I am telling you I am very new to this.
I have constructed a SP with a union statement currently it is:
-- Insert statements for procedure here
SELECT dbo.JobLink.JobID, dbo.Job.JobNumber, dbo.Job.OrderNumber, dbo.Job.BookingDate, dbo.Job.JobDescription, dbo.JobLink.LinkTable, dbo.JobLink.LinkID, dbo.JobLink.Inactive
FROM dbo.JobLink INNER JOIN
dbo.Job ON dbo.JobLink.JobID = dbo.Job.JobID
WHERE (dbo.JobLink.Inactive = 0) AND (dbo.JobLink.LinkTable = 1) AND (dbo.JobLink.LinkID = @ClientID)
UNION
SELECT dbo.JobLink.JobID, dbo.Job.JobNumber, dbo.Job.OrderNumber, dbo.Job.BookingDate, dbo.Job.JobDescription, dbo.JobLink.LinkTable, dbo.JobLink.LinkID, dbo.JobLink.Inactive
FROM dbo.JobLink INNER JOIN
dbo.Job ON dbo.JobLink.JobID = dbo.Job.JobID
WHERE (dbo.JobLink.Inactive = 0) AND (dbo.JobLink.LinkTable = 2) AND (dbo.JobLink.LinkID = @SiteID)
UNION
SELECT dbo.JobLink.JobID, dbo.Job.JobNumber, dbo.Job.OrderNumber, dbo.Job.BookingDate, dbo.Job.JobDescription, dbo.JobLink.LinkTable, dbo.JobLink.LinkID, dbo.JobLink.Inactive
FROM dbo.JobLink INNER JOIN
dbo.Job ON dbo.JobLink.JobID = dbo.Job.JobID
WHERE (dbo.JobLink.Inactive = 0) AND (dbo.JobLink.LinkTable = 3) AND (dbo.JobLink.LinkID = @WBID)
Which works fine so far...
Now I am a bit lost...
What I am needing to do is something like ...Select statement as above down to dbo.JobLink.LinkID which would be equal to any of the items of equipmentID installed on the waterbody.
I guess something like:
SELECT dbo.JobLink.JobID, dbo.Job.JobNumber, dbo.Job.OrderNumber, dbo.Job.BookingDate, dbo.Job.JobDescription, dbo.JobLink.LinkTable, dbo.JobLink.LinkID, dbo.JobLink.Inactive
FROM dbo.JobLink INNER JOIN
dbo.Job ON dbo.JobLink.JobID = dbo.Job.JobID
WHERE (dbo.JobLink.Inactive = 0) AND (dbo.JobLink.LinkTable = 4) AND (dbo.JobLink.LinkID =SELECT EQDataID, WaterBodyID
FROM dbo.Equipment
WHERE (WaterBodyID = @WBID))
I am unclear how to approach this issue.
Thanks in advance