Query must return "non-existent records" with count(*)

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 2000 Forums

SQL Server Development (2000)

Query must return "non-existent records" with count(*)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-09 : 07:14:12

Louis writes "A real stumper!
I have three tables:
Reminders:
- reminder_date
- sent_flag
- appointment_date
- appointment_time
- email
- days_prior_to_appointment
- days_prior_second
- sms_message
- cell_number
- rec_id (PK)
- sms_to
- date_loaded
- date_sent
- recurring

Company:
- company_code (PK)
- name
- credit_limit
- credit_bal
- drs_acc
- contact
- email
- phone
- address1
- address2
- system

Members:
- member_id
- first_name
- last_name
- member_login
- member_password
- email
- country_id
- state_id
- city
- zip
- address1
- address2
- address3
- phone_day
- phone_evn
- fax
- date_created
- ip_insert
- ip_update
- last_login_date
- security_devel_id
- company_code
- drs_acc
- credit_limit
- credit_bal
- act_code
- credit_adjustment
- member_type

There is only one relationship between members and Company on Company code.

I am trying to set up a query that will return the following:
company_code, company.credit_limit, system, member_id, first_name, last_name, members.credit_limit, date_sent, <recordcount>, <recordcount * 0.60>, <recordcount * 0.60 + 15> where system = 'whatever' and date_sent between <fromdate> and <todate> i.e. A list of all users each user pays 0.60 per reminder sent. The companies also pay 15.00 per user, which means I would like to list those members which also don't appear in the reminders table, so I can add 15.00 to their account this should be totalled by the amount of users in the company and added to the total 0.60 per reminder sent. Whenever I include count(*) SQA pops up an error that the fields are not included in the group by clause, but I can't include them all in the group by clause otherwise this would not make sense.
I only want to group by company and by user so I can get the total for the user and the total for the company. Also I can't manage to show the records from the members table that did not send reminders i.e. those that have no email address related to them in the reminders table.

Like I said. A real stumper. Solve it if you dare :-)

Thanks
Louis"

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-01-09 : 07:34:02

Hmm,

quote:
Also I can't manage to show the records from the members table that did not send reminders i.e. those that have no email address related to them in the reminders table.

I don't think this can be solved. There's no relation between member/reminders or company/reminders. I think you should change the design first. i.e. add a member_id in the reminders table

Getting a recordcount would be trivial using a subquery *if* there was a relationship between the tables.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-09 : 09:04:51

I agree, you should put a member_id in the reminders table.

But, to solve the problem of members without emails, I believe the current relation is via email address.

So, how about:

Select M.*, ISNULL(C,0) as EmailsSent From
Members M
LEFT OUTER JOIN
(SELECT email, COUNT(*) as C FROM Reminders GROUP BY Email) R
ON M.Email = R.Email

That returns all members, and how many emails they have been sent, including a 0 if they have not been sent any emails.

If that doesn't get you started, post the query you are testing and we can take a look. I am not 100% sure what you are trying to return.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources