CODEWITHSUNDEEP
sqlsql-server
dsb
dsb

Reputation: 2527

Avoiding creating the same query twice in SQL

I have a pretty much simple and self explanatory SQL statement:

ALTER PROCEDURE [dbo].[sp_getAllDebatesForAlias](@SubjectAlias nchar(30))
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT *
    FROM tblDebates
    WHERE (SubjectID1 in (SELECT SubjectID FROM tblSubjectAlias WHERE SubjectAlias = @SubjectAlias)) 
        OR (SubjectID2 in (SELECT SubjectID FROM tblSubjectAlias WHERE SubjectAlias = @SubjectAlias)) ;
END

I am certain that there is a way to make this statement more efficient, at least get rid of that multiple creation of the same table in the in section, i.e., the 

SELECT SubjectID FROM tblSubjectAlias WHERE SubjectAlias = @SubjectAlias

part.

Any ideas?

Upvotes: 0

Views: 113

Answers (2)

user359040
user359040

Reputation:

Try:

select d.* from tblDebates d
where exists
(select 1
 from tblSubjectAlias s
 where s.SubjectID in (d.SubjectID1, d.SubjectID2) and
       s.SubjectAlias = @SubjectAlias)

Upvotes: 1

juergen d
juergen d

Reputation: 204904

SELECT d.*
FROM tblDebates d
inner join tblSubjectAlias s on s.SubjectID in (d.SubjectID1, d.SubjectID2)
where s.SubjectAlias = @SubjectAlias

Upvotes: 1

Related Questions