Greedy
asked on
General SQL two table count
I have these two tables and I want to do a count for a condition, the problem is that I have to use two tables so it makes the results inaccurate.
select count(fsmSocialSecurity) from fsmain, fsErrorLog where (fsmTechnicianNumber = '1701') and (fsmSignOffStatus = 3) and ((fseHadTANFError=1) or (fseHadFSError=1))
I only want to count records in the fsMain table
fields that start with fsm are in the fsMain table fields that start with fse and in the fsErrorLog table.
another thing is that I am assigning this count value to a variable in Delphi by doing this
MyError := DataModule1.ErrorQuery.Fie lds[0].AsI nteger;
is this the best way?
Thanks
select count(fsmSocialSecurity) from fsmain, fsErrorLog where (fsmTechnicianNumber = '1701') and (fsmSignOffStatus = 3) and ((fseHadTANFError=1) or (fseHadFSError=1))
I only want to count records in the fsMain table
fields that start with fsm are in the fsMain table fields that start with fse and in the fsErrorLog table.
another thing is that I am assigning this count value to a variable in Delphi by doing this
MyError := DataModule1.ErrorQuery.Fie
is this the best way?
Thanks
for this query to return the correct results, you need to "join" the two tables in your query. a join is what establishes the relationship between the two tables, and is needed for the query to run correctly.
while you didn't provide a complete layout of the tables, if they have an established relationship, you just need to add the columns that make up this relationship to your "where" clause.
for example, if the two tables are related by technician number, the following query should return the correct results:
eg: select count(fsmSocialSecurity)
from fsMain, fsErrorLog
where fsmTechnicianNumber = '1701'
and fsmSignOffStatus = 3
and fsErrorLog.fseTechnicianNu mber = fsMain.fsmTechnicianNumber -- this is the "join" you need
and fseHadTANFError = 1
and fseHadFSError = 1
While the technician number may not be the way the tables are related in actuality, hopefully you get the idea. :)
HTH,
doug
while you didn't provide a complete layout of the tables, if they have an established relationship, you just need to add the columns that make up this relationship to your "where" clause.
for example, if the two tables are related by technician number, the following query should return the correct results:
eg: select count(fsmSocialSecurity)
from fsMain, fsErrorLog
where fsmTechnicianNumber = '1701'
and fsmSignOffStatus = 3
and fsErrorLog.fseTechnicianNu
and fseHadTANFError = 1
and fseHadFSError = 1
While the technician number may not be the way the tables are related in actuality, hopefully you get the idea. :)
HTH,
doug
Oops, I didn't notice that... doubas is obviously correct regarding the join.
ASKER
I still must be doing somthing wrong.
Here's the sturcture and relations
fsMain
fsmHousehold - int
fsmSocialSecurity - int
fsmReviewNumber - smallint
fsmCaseNameLast - varchar
fsmCaseNameFirst - varchar
fsmDateOfReview - datetime
fsmTechnicianNumber- varchar
fsmReviewerNumber - varchar
fsmTypeOfReview - smallint
fsmReviewPiriodFrom- datetime
fsmReviewPiriodTo - datetime
fsmSignOffStatus - smallint
fsErrorLog
fseSocialSecurity - int
fseReviewNumber - smallint
fseHadTANFError - bit
fseHadTANFDeficiency - bit
fseHadFSError - bit
fseHadFSDeficiency - bit
fseDateWorkCompleted - datetime
fseResponseNotes - text
fseResponseDate - datetime
The tables should be related with fsXSocialSecurity and fsXReviewNumber were the X is a m or e for Main or ErrorLog
These are the queries that I am using to get the counts for diferent things, they look a bit strange because I'm using Delphi to build the set MyTechSet it looks like ('1701',1702'...)
Total -
'select count(*) from fsmain where (fsmTechnicianNumber in ' + MyTechSet + ') and (fsmSignOffStatus = 3)'
# of Errors -
'select count(*) from fsmain, fsErrorLog where (fsMain.fsmSocialSecurity = fsErrorLog.fseSocialSecuri ty) and (fsmTechnicianNumber in ' + MyTechSet + ') and (fsmSignOffStatus = 3) and ((fseHadTANFError=1) or (fseHadFSError=1))'
# of Deficiency -
'select count(*) from fsmain, fsErrorLog where (fsMain.fsmSocialSecurity = fsErrorLog.fseSocialSecuri ty) and (fsmTechnicianNumber in ' + MyTechSet + ') and (fsmSignOffStatus = 3) and ((fseHadTANFDeficiency=1) or (fseHadFSDeficiency=1))'
right now I have 5 recors in the database all with the same SocialSecurity and TechnicianNumber, SigOffStatus = 3...if no errors or defs the Querries work but if one record has a error it then sayes five are in error? then if two records have errors it says 10!? what do you think it is? I've tried it from ISQL_w and my program same results
Here's the sturcture and relations
fsMain
fsmHousehold - int
fsmSocialSecurity - int
fsmReviewNumber - smallint
fsmCaseNameLast - varchar
fsmCaseNameFirst - varchar
fsmDateOfReview - datetime
fsmTechnicianNumber- varchar
fsmReviewerNumber - varchar
fsmTypeOfReview - smallint
fsmReviewPiriodFrom- datetime
fsmReviewPiriodTo - datetime
fsmSignOffStatus - smallint
fsErrorLog
fseSocialSecurity - int
fseReviewNumber - smallint
fseHadTANFError - bit
fseHadTANFDeficiency - bit
fseHadFSError - bit
fseHadFSDeficiency - bit
fseDateWorkCompleted - datetime
fseResponseNotes - text
fseResponseDate - datetime
The tables should be related with fsXSocialSecurity and fsXReviewNumber were the X is a m or e for Main or ErrorLog
These are the queries that I am using to get the counts for diferent things, they look a bit strange because I'm using Delphi to build the set MyTechSet it looks like ('1701',1702'...)
Total -
'select count(*) from fsmain where (fsmTechnicianNumber in ' + MyTechSet + ') and (fsmSignOffStatus = 3)'
# of Errors -
'select count(*) from fsmain, fsErrorLog where (fsMain.fsmSocialSecurity = fsErrorLog.fseSocialSecuri
# of Deficiency -
'select count(*) from fsmain, fsErrorLog where (fsMain.fsmSocialSecurity = fsErrorLog.fseSocialSecuri
right now I have 5 recors in the database all with the same SocialSecurity and TechnicianNumber, SigOffStatus = 3...if no errors or defs the Querries work but if one record has a error it then sayes five are in error? then if two records have errors it says 10!? what do you think it is? I've tried it from ISQL_w and my program same results
ASKER
oh I think I got it...I forgot to add the Review number too -
and (fsMain.fsmReviewNumber = fsErrorLog.fseReviewNumber )
Would you like to recieve the points doubas?
Thanks
and (fsMain.fsmReviewNumber = fsErrorLog.fseReviewNumber
Would you like to recieve the points doubas?
Thanks
yes, i would - i guess you need to reject Jim_SQL's answer and i will submit an answer that you can then accept? i'm new to this, so i'm not sure if that's the way it should work or not. let me know.
also, may i make one more suggestion? imho, you should move the location of your joins in your where clauses so that the joins do not occur until after you have screened out a portion of the data. look at the example below:
select count(*)
from fsmain, fsErrorLog
where (fsmTechnicianNumber in ' + MyTechSet + ')
and (fsmSignOffStatus = 3)
and (fsMain.fsmSocialSecurity = fsErrorLog.fseSocialSecuri ty)
and ((fseHadTANFError=1) or (fseHadFSError=1))
in this query, the join should not occur until you have already narrowed down the rows in fsMain to the proper technician number and sign-off status. if you put the join first, then all rows n the two tables would be joined, with some/most of them being discarded later after you specify the technician number/sign-off status.
it's true that the query optimizer would probably handle it automatically for you, but i think it's a good habit to assume that it won't.
doug
also, may i make one more suggestion? imho, you should move the location of your joins in your where clauses so that the joins do not occur until after you have screened out a portion of the data. look at the example below:
select count(*)
from fsmain, fsErrorLog
where (fsmTechnicianNumber in ' + MyTechSet + ')
and (fsmSignOffStatus = 3)
and (fsMain.fsmSocialSecurity = fsErrorLog.fseSocialSecuri
and ((fseHadTANFError=1) or (fseHadFSError=1))
in this query, the join should not occur until you have already narrowed down the rows in fsMain to the proper technician number and sign-off status. if you put the join first, then all rows n the two tables would be joined, with some/most of them being discarded later after you specify the technician number/sign-off status.
it's true that the query optimizer would probably handle it automatically for you, but i think it's a good habit to assume that it won't.
doug
ASKER
Yeah, that sounds like a good idea. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DECLARE @v int
select @v = count(fsmSocialSecurity)
from fsmain, fsErrorLog
where fsmTechnicianNumber = '1701'
and fsmSignOffStatus = 3
and ((fseHadTANFError=1) or (fseHadFSError=1))
select @v
The total that is given will only count the records that match between the tables (records have to be in fsMain table). If fsmSocialSecurity can ever be NULL then you probably want to do a count(*) instead of a count(fsmSocialSecurity). I don't know Delphi, but the above code is what I'd use for MS SQL 6.5 as far as assigning the value goes.
Hope this helped.
Jim