condition an inner join

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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 15-Jan-13 21:34pm

DanaH85

Add a Solution

2 solutions

Solution 1

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.

Posted 15-Jan-13 21:40pm

phil.o

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

sisir patro · Accepted Answer · 2013-01-15T21:46:00

Solution 2

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

Posted 15-Jan-13 21:46pm

sisir patro

Updated 15-Jan-13 21:47pm

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.