December 15, 2004 at 10:59 am
Hi
In SS2000, I want to see if the employid in transaction_table is exist in DD00100 table, and I also want to include EAcct_Num from Transaction table.
The DD00100 table is the master table, do duplicate employid in this table. However, the key in the transaction_table is Batch_Num and employid. For example, in Batch #10, it has employid= '123456789' which can exist in other batch number as well, but same employid can't have duplicate in same batch_num.
I wrote this query, but it returns
CountID employid EAcct_Num
----------- --------------- ---------------
4 335908462 NULL
1 354663418 397002730398
because transaction_table has total 4 employid=335908462 from 4 different batch_num. But I want to see if there's any record in DD00100, which should return either 1 or 0 for the CountID.......
what should I do to correct this problem? Thanks.
The SQL:
--------------------------------------------------------
SELECT COUNT(*) AS CountID,D.employid, T.EAcct_Num
FROM HFC.dbo.DD00100 D, HFC_Epay.dbo.Transaction_Table T
where D.EmployID=T.Employid
and D.EmployID in (select distinct T.EmployID from HFC_Epay.dbo.Transaction_Table T where T.Batch_Num= 15)
group by D.employid,T.EAcct_Num
December 15, 2004 at 1:36 pm
It's not clear what you're really trying to accomplish, but this can do what you're asking:
select a.key1, a.id, COUNT( b.id )
from table1 a
left outer join idlookuptable b ON a.id = b.id
GROUP BY a.key1, a.id,
outer joining to the id lookup table will include all rows in the first table and have a null in b.id when not there. Count will evaluate the null as 0 so that 3rd column will have a 0 or 1 in it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply