LEFT JOIN and COMPARE VALUES in WHERE CONDITION

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

SQL Server 2005 Forums

Transact-SQL (2005)

LEFT JOIN and COMPARE VALUES in WHERE CONDITION

Author

Topic

byomjan
Starting Member

34 Posts

Posted - 2009-12-21 : 14:10:42

TABLE EMP
(EMP_NAME VARCHAR(20),
EMP_ROLE VARCHAR(10),
EMP_LOAN INT)

'ROB','CLERK', 1000
'TOM','MANAGER' 4000
'HILL','', 3000
'JULIAN', 'LEAD',5000

TABLE APPROVAL_LOOKUP
( EMP_ROLE VARCHAR(20),
APPROVED_LOAN INT)

'CLERK', 1000
'MANAGER' 4000
'AUTO', 500
'LEAD',5000

Condition - If EMP_ROLE is missing, it should be taken as 'AUTO' and then
b.APPROVED_LOAN >a.EMP_LOAN should be evaluated.
I am not able to do it. Can you help .

my try

SELECT a.EMP_NAME, isnull(a.EMP_ROLE, 'AUTO') ,a.EMP_LOAN , b.APPROVED_LOAN FROM
EMP a
LEFT JOIN APPROVAL_LOOKUP b
ON a.EMP_ROLE =b.EMP_ROLE
WHERE b.APPROVED_LOAN >a.EMP_LOAN

Byomjan....

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-21 : 14:20:25

I revised your query because it didn't make sense with the logic illustrated.

quote:

Condition - If EMP_ROLE is missing, it should be taken as 'AUTO' and then
b.APPROVED_LOAN >a.EMP_LOAN should be evaluated.
I am not able to do it. Can you help

If Emp_Role is missing there would not be a value for b.Approved_Loan, I think you meant if it's AUTO then return the record, if it's not then make sure a.approved_loan is > a.emp_Loan?


SELECT a.EMP_NAME, isnull(b.EMP_ROLE, 'AUTO') ,a.EMP_LOAN , b.APPROVED_LOAN 
FROM 
	EMP a
LEFT JOIN 
	APPROVAL_LOOKUP b
ON a.EMP_ROLE =b.EMP_ROLE 
WHERE 
(	not b.Emp_Role is null
 and
	a.APPROVED_LOAN > a.EMP_LOAN 
)
or b.Emp_Role is null

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-21 : 14:22:40

This?

declare @EMP TABLE
(EMP_NAME VARCHAR(20), 
EMP_ROLE VARCHAR(10),
EMP_LOAN INT)

insert @EMP
select 'ROB','CLERK', 1000
union all select 'TOM','MANAGER' ,4000
union all select 'HILL','', 3000
union all select 'JULIAN', 'LEAD',5000

DECLARE @APPROVAL_LOOKUP TABLE 
( EMP_ROLE VARCHAR(20),
APPROVED_LOAN INT)
INSERT @APPROVAL_LOOKUP

SELECT 'CLERK', 1000
union all select 'MANAGER' ,4000
union all select 'AUTO', 5000
union all select 'LEAD',5000

SELECT a.EMP_NAME, b.EMP_ROLE ,a.EMP_LOAN , b.APPROVED_LOAN FROM 
@EMP a
INNER JOIN @APPROVAL_LOOKUP b
ON COALESCE(NULLIF(a.EMP_ROLE,''),'AUTO') = b.EMP_ROLE 
WHERE b.APPROVED_LOAN >= a.EMP_LOAN

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-21 : 14:27:08

My interpretation was OP wants this condition "b.APPROVED_LOAN >a.EMP_LOAN" to be evalutaed for all rows, and , for rows that did not have the EMP_ROLE valued, the APPROVED_LOAN value shud be taken from the value corresponding to role 'AUTO'...

quote:
Originally posted by Vinnie881

I revised your query because it didn't make sense with the logic illustrated.

quote:

Condition - If EMP_ROLE is missing, it should be taken as 'AUTO' and then
b.APPROVED_LOAN >a.EMP_LOAN should be evaluated.
I am not able to do it. Can you help

If Emp_Role is missing there would not be a value for b.Approved_Loan, I think you meant if it's AUTO then return the record, if it's not then make sure a.approved_loan is > a.emp_Loan?

SELECT a.EMP_NAME, isnull(b.EMP_ROLE, 'AUTO') ,a.EMP_LOAN , b.APPROVED_LOAN
FROM
EMP a
LEFT JOIN
APPROVAL_LOOKUP b
ON a.EMP_ROLE =b.EMP_ROLE
WHERE
( not b.Emp_Role is null
and
a.APPROVED_LOAN > a.EMP_LOAN
)
or b.Emp_Role is null

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

byomjan
Starting Member

34 Posts

Posted - 2009-12-21 : 15:03:19

insert @EMP
select 'ROB','CLERK', 1000
union all select 'TOM','MANAGER' ,4000
union all select 'HILL','', 3000
union all select 'JULIAN', 'LEAD',5000

I think the 3rd row should be
union all select 'HILL',NULL, 3000

Byomjan....

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-21 : 15:08:08

Your sample data shows empty string..which is why I included '' instead of NULL. But it doesn't matter. did you try the query?

byomjan
Starting Member

34 Posts

Posted - 2009-12-21 : 15:09:35

SELECT a.EMP_NAME, a.EMP_ROLE,isnull(a.EMP_ROLE, 'AUTO') ,
a.EMP_LOAN , b.EMP_ROLE, b.APPROVED_LOAN
FROM
EMP a
JOIN APPROVAL_LOOKUP b
ON isnull(a.EMP_ROLE, 'AUTO') =b.EMP_ROLE
WHERE b.APPROVED_LOAN >a.EMP_LOAN

Byomjan....

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-21 : 15:11:44

You do not need SELECT a.EMP_NAME, a.EMP_ROLE,isnull(a.EMP_ROLE, 'AUTO')

just SELECT a.EMP_NAME, a.EMP_ROLE,a.EMP_ROLE would do as you are accounting for the NULLs in the JOIN.

quote:
Originally posted by byomjan

SELECT a.EMP_NAME, a.EMP_ROLE,isnull(a.EMP_ROLE, 'AUTO') ,
a.EMP_LOAN , b.EMP_ROLE, b.APPROVED_LOAN
FROM
EMP a
JOIN APPROVAL_LOOKUP b
ON isnull(a.EMP_ROLE, 'AUTO') =b.EMP_ROLE
WHERE b.APPROVED_LOAN >a.EMP_LOAN

Byomjan....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources