| Author |
 Topic  |
|
|
pushp82
Yak Posting Veteran
67 Posts |
 Posted - 02/25/2012 : 01:46:14
|
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
5152 Posts |
Posted - 02/25/2012 : 03:30:07
|
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
ENDHOWEVER, 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. |
 |
|
|
pushp82
Yak Posting Veteran
67 Posts |
Posted - 02/25/2012 : 06:26:06
|
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
ENDHOWEVER, 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 .....
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 02/25/2012 : 08:42:03
|
Of course! <-- That is supposed to be a sheepish grin
Glad you figured it out!! |
|
|
| |
Topic |
|
|
|
Join based on condition
