Join based on condition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join based on condition

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-02-25 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-25 : 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
   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.

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-02-25 : 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
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 .....

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-25 : 08:42:03

Of course!

<-- That is supposed to be a sheepish grin

Glad you figured it out!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources