Member Avatar for nickg21

Hey everyone, I have the following two queries, and want to know why the first one would return a count of 52, but the second would return a count of 51 when technically they should be the same;

SELECT count(*) from Inquiry
Inner Join NoDecision on Inquiry.ID = NoDecision.InquiryID_fk
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
WHERE (Inquiry.DecisionMade = 'No decision yet') AND
(Inquiry.Date >= '4/1/2012' AND Inquiry.Date <= '9/17/2012') AND
Inquirer.Program IN('Appleton','Brook','Fernside','Gunderson','Lincoln','Pavilion')

SELECT count(*) from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
WHERE (DecisionMade = 'No decision yet') AND
(Inquiry.Date >= '4/1/2012' AND Inquiry.Date <= '9/17/2012') AND
Inquirer.Program IN('Appleton','Brook','Fernside','Gunderson','Lincoln','Pavilion')

I understand that I am joining the NoDecision table, but I don't understand why that would increase the count by 1 in the first query.
Any help would be appreciated.
Thanks,
NickG

  • 2 Contributors
  • 3 Replies
  • 140 Views
  • 12 Minutes Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for urtrivedi

I think NoDecision is having 2 inquiry.id for any one case so it is adding one more

you can try

count (distint Inquiry.ID)
Member Avatar for nickg21

I tried doing that as well, I should have posted it with the DISTINCT, the counts still remain different by 1.
Thanks for your quick suggestion though

Member Avatar for urtrivedi

can u post sql script of 3 table with data for that condition

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.