|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, December 04, 2009 2:18 AM
Points: 6,
Visits: 26
|
|
LEFT OUTER JOIN returns each row that satisfies the join of the first table with the second table.
It also returns any rows from the first table that had no matching rows in the second table.
The nonmatching rows in the second table are returned as null values.
Is it possible that instead of returning non matching as NULL, system returns value 1.
Even if table B's row does not match, for one specific column, instead of NULL I want system
to return the value 1 for that column, if the column's original value is say ... greater than 1000
Thanks
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 4,973,
Visits: 3,912
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, December 04, 2009 2:18 AM
Points: 6,
Visits: 26
|
|
hey thanks.
I shall try out isnull tomorrow in office.
Isnull will check the returned value is null or not.
what is I have to check two conditions.
I need to check whether the returned value is NULL and also whether the original value in the column was null or not.
Is there a way I can add a case statement to the following ... Isnull(table.col,1) ... to check that aslo
Thanks.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, December 04, 2009 2:18 AM
Points: 6,
Visits: 26
|
|
hi ALZDBA,
Let me make my requirement more specfic.
I have to perform left outer join between table a and table b.
now we know that there can be rows in 'table b' for which comparison do not match in the ON clause.
in my application, I want to find out whether the table B' col1 did not match because
the value was in itself greater than 1000.
In that case only I want to override with value 1.
So the query's result will have three types of values returned in tableB.col1
a. 'actual value' because lett outer join condition matched
b. 'null' because left outer join condition did not match
c. 'actual value' because though condition did not match, the original value was greatet than 1000
Thanks,
maneesh
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 4,973,
Visits: 3,912
|
|
off course you can add the extra case to evaluate the > 1000.
Keep in mind to actually evaluate on one of the join condition key columns of your B table. Because they will only be null if your condition didn't match.
It is possible your row non-key column(s) IS actually NULL. Do you want to replace that NULL also with a replacement value ??
 Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere 
Very usefull HowTo for forums:
- How to post Performance Problems
- How to post data/code to get the best help
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Friday, December 04, 2009 2:18 AM
Points: 6,
Visits: 26
|
|
I guess i was going in the wrong direction totally.
say there are three rows in table B whose col1 value is greater than 1000.
these three rows in table B will never be retrieved by left outer join because condition in the
on clause will never match between table A and table B.
I was thinking there must be some way I can retrieve additional information from table B ... by additional I mean the thee rows which do not meet the criteria on ON CLAUSE but its value is greate than 1000.
I might have to use an 'OR CLAUSE' in the 'ON CONDITION' which can hamper performance.
Regards,
Maneesh
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Thursday, February 11, 2010 6:24 AM
Points: 19,
Visits: 64
|
|
If you need all the rows from table A with (optional) matching rows from table B and also certain rows from table B you could use a full outer join:
select isnull( A.col1 , B.col1 ) as result
from A full outer join B on A.col1 = B.col1
where ( A.col1 is not null or B.col1 > 1000 )
This statement will return all rows from table A that have a value in col1 and all rows from table B with a value in col1 above 1000 that does not exist in table A. If col1 may be null in table A you might consider replacing it in the where-clause only with another column of table A that does not contain nulls, for example a primary key column.
|
|
|
|
