Avoiding creating the same query twice in SQL

Question

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?

user359040 · Accepted Answer · 2012-11-21 13:00:12Z

1

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)

answered Nov 21, 2012 at 13:00

user359040

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2012-11-21 12:53:33Z

1

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

answered Nov 21, 2012 at 12:53

juergen d

205k40 gold badges305 silver badges377 bronze badges

2 Comments

user359040 Over a year ago

Not quite the same; if there are multiple subjects with the same alias, each Debate record may be returned multiple times (once for each matching subject).

juergen d Over a year ago

You are right. A select distinct ... might be suitable for the OPs needs.

Collectives™ on Stack Overflow

Avoiding creating the same query twice in SQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related