Click here to Skip to main content
15,847,077 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

I have a DB built as following:
Inactive Table - including all deleted\Inactive users
Active Table - including all currently active users
Main Table - including all users (both active and inactive)

some fields exists in all three tables (Such as userName), and I would like to take the data from the Inactive or Active table (Since the data is not always equal in all of the tables), so I want to have a join which will be either from the Active table or Inactive table (according to IsActive field in the main table)


SQL
SELECT M.ID, A.userName 
FROM MainTbl M
IF M.IsActive = 1
THEN
    INNER JOIN ActiveTbl A ON A.UserID = M.ID
ELSE
    INNER JOIN InactiveTbl A ON A.UserID = M.ID


Pay attention that in both join I use A

Is there any way to do something like that?
Posted

Yes this is possible, but you have to do an UNION.
Like this :

SQL
SELECT
   M.ID
  ,A.Username
FROM MainTbl M INNER JOIN ActiveTbl A ON A.UserID = M.ID
WHERE M.IsActive = 1

UNION

SELECT
   M.ID
  ,A.Username
FROM MainTbl M INNER JOIN InactiveTbl A ON A.UserID = M.ID
WHERE M.IsActive = 1


Hope this helps. Thanks to vote anyway.
Regards.
 
Share this answer
 
Comments
DanaH85 16-Jan-13 4:02am    
Thanks for the idea. It's good for the example query, it's just that my query is actually HUGE and its also really heavy, and the UNION will make it heavier.
phil.o 16-Jan-13 4:42am    
I can imagine it will make it heavier, if your first query is already a huge one.
But I cannot think of another solution, from the requirements you gave. As far as I know, it is not possible to condition a JOIN ; if you want both parts, you have to 'unite' both queries.
HI,

Change your query to a dynamic query like:

SQL
DECLARE @SqlQuery VARCHAR(MAX)


set @SqlQuery = 'SELECT M.ID, A.userName
                 FROM MainTbl M'
                 if exists(select M.IsActive from MainTbl M)
                 begin
               set @SqlQuery = @SqlQuery + 'INNER JOIN ActiveTbl A ON A.UserID = M.ID'
                   end
                 else
                 begin
                  set @SqlQuery = @SqlQuery + 'INNER JOIN InactiveTbl A ON A.UserID = M.ID'
                   end
exec @SqlQuery

Thanks
 
Share this answer
 
v2
Comments
DanaH85 16-Jan-13 4:12am    
two more little question -
1. If the query supposed to return all users (some are active and some are not) will this solution get me all of those users properly?
2. What if the field was not boolean, but string, how will this work then?
[no name] 16-Jan-13 4:42am    
Whether the field is string or boolean it will work. You have to create your condition of your own according to your requirement.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900