SQLTeam.com | Weblogs | Forums

Very New To SQL Issue with SP and Multiple Records Search

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

hi

Does this help you !!! :slight_smile:

select
abc.*
from
(
select statement 1
union all
select statement 2
union all
select statement 3
) abc
where abc.column1 = (select column1 from table2 )

Try this:

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 IN (
		SELECT EQDataID
		FROM dbo.Equipment
		WHERE WaterBodyID = @WBID
		)

Thanks Wim

I got it figured out but didnt get back to mark up. This indeed was the solution.