SQL, nested queries, subqueries, correlated queries

Question

SELECT T.[NAME] FROM  ( 
     SELECT Unregistered.[NAME] 
     FROM Unregistered 
        LEFT JOIN [Event] ON Event.ID = Unregistered.EventID 
     WHERE EventID IN ( 
                       SELECT EventID 
                       FROM Registered WHERE [NAME] Like '%blabla%' )
     UNION ALL
     SELECT Registered.[NAME] 
     FROM Registered
        LEFT JOIN [Event] ON Event.ID = Registered.EventID
     WHERE EventID IN ( 
                       SELECT EventID 
                       FROM Registered WHERE [NAME] Like '%blabla%' )
) AS T

So, I have a table, lets call it Events that is linked to tables Unregistered and Registered. I want to select all Unregistered AND Registered rows in ANY EVENT that is found by the content in this Registered table.

Both Unregistered and Registered -tables have several (or none) entries with the same Event ID. This is just demo content and names, yet the problem is real.

You see the problem, here I need to query twice for the ID range, how can I make this in one query and use the result in both union statements? As in whats the best and most efficient way to perform a query for the output?

It's MS SQL Server.

Thanks!

Answer 1

You can reorganize the query a little to avoid the two identical sub-queries.

SELECT t.[NAME]
FROM
    (
    SELECT [EventID], [Unregistered].[NAME]
    FROM [Unregistered]
    UNION ALL
    SELECT [EventID], [Registered].[NAME]
    FROM [Registered]
    ) t
LEFT OUTER JOIN [Event] ON t.[EventID] = [Event].[ID]
WHERE t.[EventID] IN ( SELECT [EventID] FROM [Registered] WHERE [NAME] Like '%blabla%' )