Join when value <> 0 - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Join when value <> 0

Author

Topic

bkbinani
Starting Member

15 Posts

Posted - 2011-12-22 : 08:31:04

Dear Experts,

i wish to connect master table with transaction with condition that
transcation table id value <> 0. when Zero it sud return *** as value.Since My Table may have id = 0 or 1,2,5,8,99 etc
I was trying as under but not suceess.

Plz Help.


-- dat_itm_detail --> Transaction Table
-- mst_led --> Master
-- dat_itm_detail.iagent_id  may have 0 value means no records in master
--   but valid transaction without AGENT.
select mst_led.cled_name,
       dat_itm_detail.uitem_trnid,
       dat_itm_detail.iagent_id
from  dat_itm_detail
join  mst_led on mst_led.iled_autono = iagent_id
where dat_itm_detail.ivch_type=1

Executing this skip few records where agent_id=0. But I wish to have all those records too.

Plz guide me.

TIA

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-22 : 09:09:42

Try using a left join as in

-- dat_itm_detail --> Transaction Table
-- mst_led --> Master
-- dat_itm_detail.iagent_id  may have 0 value means no records in master
--   but valid transaction without AGENT.
select mst_led.cled_name,
       dat_itm_detail.uitem_trnid,
       dat_itm_detail.iagent_id
from  dat_itm_detail
LEFT join  mst_led on mst_led.iled_autono = iagent_id
where dat_itm_detail.ivch_type=1

bkbinani
Starting Member

15 Posts

Posted - 2011-12-26 : 01:34:01

Thanx Sir.

It worked.

Very Nice & Cool.

Hope 2 c u again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources