why does this return different count

Question

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

No they are not the same.

The first one only returns rows where dist.cdedoctype equals 'INV'.

The second one returns rows where dist.cdedoctype equals 'INV' OR has a NULL value.

The first one won't count NULLs.

-SQLBill

vidya-391318 SSC-Addicted Points: 407 More actions · Answer 2

thanks for the quick reply

but this is my question

this is the query i have on my older version of SQL

select count(*)

from nameaddr_ac addr, recipients rec, RecipientDistType dist

where

rec.idaccount = addr.id_account

and rec.idaccount*=dist.idaccount

and rec.cdeRecipientType = 'Market'

and dist.cdedoctype='INV'

what should be the equivalent for this in the latest version of SQL ???

thanks,

vidya

Sergiy SSC Guru Points: 110209 More actions · Answer 3

I don't think mixing syntax is a good idea.

Try to make it consistent:

select count(*)

from nameaddr_ac addr

INNER JOIN recipients rec ON rec.idaccount = addr.id_account and rec.cdeRecipientType = 'Market'

left outer join RecipientDistType dist on rec.idaccount = dist.idaccount and dist.cdedoctype='INV'

_____________
Code for TallyGenerator