| Left outer Join nt working as desired [message #615547] |
Thu, 05 June 2014 05:44  |
 |
raj_te
Messages: 46
Registered: August 2013
Location: INDIA
|
Member |
|
|
Hi ,
I am using one query and not getting the correct result. Table int_indices has 6000 unique records.Table int_index_currency_det has 10000 records. I want to make a left outer join between int_indices and int_index_currency_det such that all the records with same index id and vendor_id gets picked up. So finally my result set should contain 6000 records. Here the b.status_in = 'A' is not working as desired. In the result set getting inactive reords as well. Please help.
select b.*, bc.Currency_id
from int_indices b
left join
int_index_currency_det bc
on b.index_id = bc.index_id
and b.vendor_id = bc.vendor_id
and b.current_rec_in = Y
and bc.current_rec_in = Y
and b.status_in = 'A'
and bc.status_in = 'A'
and bc.currency_type_id = 'PRIMARY'
|
|
|
|
|
|
| Re: Left outer Join nt working as desired [message #615552 is a reply to message #615550] |
Thu, 05 June 2014 06:14  |
|
raj_te
Messages: 46
Registered: August 2013
Location: INDIA
|
Member |
|
|
Sir,
Even after putting that way am not getting the appropriate records count.
SELECT b.*,
bc.currency_id
FROM int_indices b
left join int_index_currency_det bc
ON b.index_id = bc.index_id
AND b.vendor_id = bc.vendor_id
AND bc.currency_type_id = 'PRIMARY'
WHERE b.current_rec_in = 'Y'
AND bc.current_rec_in = 'Y'
AND b.status_in = 'A'
AND bc.status_in = 'A'
[Updated on: Thu, 05 June 2014 06:15] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Left outer Join nt working as desired [message #615594 is a reply to message #615559] |
Thu, 05 June 2014 13:45 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Lalit Kumar B wrote on Thu, 05 June 2014 12:51You have the filter predicate AND bc.currency_type_id = 'PRIMARY' in the join clause instead of where clause.
That's probably in the correct place. If you move it to the where clause it'll render the outer-join pointless since 'PRIMARY' != null (made up by outer join).
@raj_te - all restrictions against int_index_currency_det should probably be in the join clause and all restrictions against int_indices should be in the where clause.
If that doesn't give what you want then you're going to have to actually describe the relationship between the two tables to us. It's very difficult to guess what code you know nothing about, and doesn't work, is supposed to do.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
