SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Join based on condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pushp82
Yak Posting Veteran

81 Posts

Posted - 02/25/2012 :  01:46:14  Show Profile  Reply with Quote
Hi Friends,

I have a problem as under:

SELECT A,B,C,D,E FROM TBL_ABC
INNER JOIN TBL_XYZ ON A=X
"
NOW NEED TO JOIN TBL_PQR (age1,age2) further
based on E. E is of bit type
the condition is
if E is 1 then join

inner join tbl_pqr on B=age1
if E is 2 then join

inner join tbl_pqr on B=age2
"

How to do this? Please help me ASAP and thanks in advance.....

Pushp

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 02/25/2012 :  03:30:07  Show Profile  Reply with Quote
You can write the join condition like this:

SELECT A,B,C,D,E FROM TBL_ABC
INNER JOIN TBL_XYZ ON A=X
INNER JOIN TBL_PQR ON
   CASE 
      WHEN E = 1 THEN B = age1
      WHEN E = 2 THEN B = age2
   END
HOWEVER, I have several concerns about this (listed in no particular order)

1. Bit data type can store only a 0 or a 1. So you would not have a 2
2. If TBL_PQR has only two columns age1 and age2, what is the purpose of joining? Are you trying to pick out specific combinations of dates?
3. age1 and age2 sounds like they are numbers indicating how old someone is. Usually when you join, you join on columns that are candidate keys (for example, employeeId etc.) and not on attributes such as employee_age.
4. You should qualify the column names with the table aliases - it is a generally recommended practice, for a variety of reasons. For example:
SELECT t1.A,t1.B,t1.C,t2.D,t2.E FROM TBL_ABC as t1
INNER JOIN TBL_XYZ as t2 ON t1.A=t2.X

If you describe the business problem you are trying to solve people on this forum may be able to suggest solutions.
Go to Top of Page

pushp82
Yak Posting Veteran

81 Posts

Posted - 02/25/2012 :  06:26:06  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

You can write the join condition like this:

SELECT A,B,C,D,E FROM TBL_ABC
INNER JOIN TBL_XYZ ON A=X
INNER JOIN TBL_PQR ON
   CASE 
      WHEN E = 1 THEN B = age1
      WHEN E = 2 THEN B = age2
   END
HOWEVER, I have several concerns about this (listed in no particular order)

1. Bit data type can store only a 0 or a 1. So you would not have a 2
2. If TBL_PQR has only two columns age1 and age2, what is the purpose of joining? Are you trying to pick out specific combinations of dates?
3. age1 and age2 sounds like they are numbers indicating how old someone is. Usually when you join, you join on columns that are candidate keys (for example, employeeId etc.) and not on attributes such as employee_age.
4. You should qualify the column names with the table aliases - it is a generally recommended practice, for a variety of reasons. For example:
SELECT t1.A,t1.B,t1.C,t2.D,t2.E FROM TBL_ABC as t1
INNER JOIN TBL_XYZ as t2 ON t1.A=t2.X

If you describe the business problem you are trying to solve people on this forum may be able to suggest solutions.



Hi Snitabeck,

The code worked but with little manipulate as

SELECT A,B,C,D,E FROM TBL_ABC
INNER JOIN TBL_XYZ ON A=X
INNER JOIN TBL_PQR ON B=
CASE
WHEN E = 1 THEN age1
WHEN E = 2 THEN age2
END

secondly your r right that my column was not of bit type but int.I had only the value as 1 and 2 as status because of business logic.

next, the column as age1 and age2 was my imagination to make the query simplest to understand, so the other people coming for solution on sqlteam can get it easily, actually the values are some integer into a big table.

next, now i will practice to use table aliases with columns.

at last as usual thank to u to provide tips with solution, u r always so helpfull .....
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 02/25/2012 :  08:42:03  Show Profile  Reply with Quote
Of course! <-- That is supposed to be a sheepish grin

Glad you figured it out!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000