Improve query with WHERE clause using same subquery multiple times

Question

I've got a SQL query I fail to improve right now. It works, but it's a bit ugly.

I want to fetch:

• an id from table A
• a name from table B

using a WHERE clause which in itself fetches a value from another SQL query.

I would like to replace the following two instances of this SQL query used for the WHERE clause, with one instance:

SELECT intImageGalleryID FROM tblEPiServerCommunityImageGalleryImage
WHERE intID = 123123

How can it be done?

Using SQL Server.

Here's the complete SQL query:

SELECT intID,   
        (SELECT strName
        FROM tblEPiServerCommunityImageGallery
        WHERE intID = 
            (SELECT intImageGalleryID
            FROM tblEPiServerCommunityImageGalleryImage
            WHERE intID = 123123)
            ) as name   
FROM tblEPiServerCommunityClub
    WHERE intImageGalleryID =   
        (SELECT intImageGalleryID
        FROM tblEPiServerCommunityImageGalleryImage
        WHERE intID = 123123)

Thanks!

Answer 1

You could try below query. I think it is equivalent with your original solution.

SELECT  scc.intID
        , sci.strName
FROM    tblEPiServerCommunityClub scc
        INNER JOIN tblEPiServerCommunityImageGalleryImage scig ON scig.intImageGalleryID = scc.intImageGalleryID
        INNER JOIN tblEPiServerCommunityImageGallery sci ON sci.intID = scig.intImageGalleryID
WHERE   scig.intID = 123123