Send an e-mail to one person who has multiple products

Hey Experts!!
I have asked this question before, but I am still having issues with writing this query.

What I have is a database with 5 or 6 tables that are all related via a couple of keys. When the query is ran, you get a list of results composed of the following fields:

Client Table
"      Client name
"      Address
"      City
"      State
"      Zip
"      Etc
"      Client Key

Product Table
"      Serial
"      Primary Key
"      Client Key
"      Model Key
"      Etc

Contact table
"      First name
"      Last name
"      Phone numbers
"      E-mails
"      Etc.

The problem is that one customer could have 80 products and with the current SQL would receive 80 e-mails.  Is there a way to include all of their products in 1 shot without sending them 80 e-mails.  

My SQL:
SELECT DISTINCT (AED_Master.ACT_no) AS Expr1, Client.ACT_no, *
FROM (((Contacts RIGHT JOIN Client ON Contacts.ACT_No=Client.ACT_no)  
LEFT JOIN AED_master ON Client.ACT_no=AED_master.ACT_No)  
LEFT JOIN Models ON AED_master.Model_ID=Models.Model_ID)
LEFT JOIN Cat_Sub ON Client.CatSub_id = Cat_Sub.CatSub_id
WHERE (((Client.ACT_no)=?));

With the above SQL, I am still getting duplicate results.  I need to tame this so it is one contact/multiple products.
I can upload the Database if that would help, just let me know.  I am hoping this can be solved via a simple SQL variation, or if someone can write it for me and let me know why/what we did that?
LVL 30
LZ1Asked:
Who is Participating?
 
paisleymConnect With a Mentor Commented:
Hi LZ1.

You may have solved this already. If you are getting too many results, then some of the select and group by fields need to be dropped. Without a whole lot of guessing about which fields are on which tables, it is difficult to advise which ones, maybe start with Models.Model and Models.Model_Id, etc.

Marcelle
0
 
paisleymCommented:
Hi

You need to group by the ACT_no, and ergo, the other fields you want to extract. So, remove  the distinct, replace  * with the actual fields you want, and add a group by at the end listing all extracted fields.

Marcelle
0
 
LZ1Author Commented:
The main problem with that is that I am not sure which fields the end-user will ultimately need.  Can I leave the *? Or would I have to list each field from each table??
 
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
LZ1Author Commented:
Here is my new SQL:
SELECT Client.ACT_no, Client.Client, Client.Address, Client.Address2, Client.City, Client.State, Client.Zipplus, Client.County, Client.Area_Phone, Client.CatSub_ID, Client.Contact_no,
AED_Master.AED_no, AED_Master.ACT_no, AED_Master.Serial_Code, AED_Master.Model_ID, AED_Master.Adult_pads, AED_Master.Battery, AED_Master.Ped_Pads, AED_Master.Spare_Adult_Pads, AED_Master.Spare_Battery, AED_Master.Spare_Ped_Pads, AED_Master.Placement,
Models.Model_ID, Models.Model,
Cat_Sub.CatSub_ID, Cat_Sub.Category, Cat_Sub.Subgroup,
Contacts.ACT_no, Contacts.contact_salutation, Contacts.contact_first,  Contacts.contact_last,  Contacts.contact_position,  Contacts.contact_county,  Contacts.contact_phone1,  Contacts.contact_phone1_ext,  Contacts.contact_phone2,  Contacts.contact_phone2_ext,  Contacts.contact_fax,  Contacts.contact_email1,  Contacts.contact_email2
FROM (((CLIENT LEFT JOIN AED_MASTER ON AED_MASTER.ACT_NO=CLIENT.ACT_NO)
LEFT JOIN MODELS ON AED_MASTER.MODEL_ID=MODELS.MODEL_ID)
INNER JOIN CAT_SUB ON CLIENT.CATSUB_ID=CAT_SUB.CATSUB_ID)
LEFT JOIN Contacts ON Client.ACT_no=Contacts.ACT_no
WHERE ((Client.ACT_no) = 5132);
Still getting duplicate results.......any suggestions?
0
 
paisleymCommented:
Hi

Yes, you are still getting the same results because you don't have a group statement. I understand you don't know which fields the customer wants to see, but your original request is to only get a unique email for each customer that has products. give them the option to click a button to see the full details. (Guessing which fields you need to group by)

SELECT Client.ACT_no, Client.Client, Client.Address, Client.Address2, Client.City, Client.State, Client.Zipplus, Client.County, Client.Area_Phone, Client.CatSub_ID, Client.Contact_no,
Cat_Sub.Category, Contacts.contact_email1,  Contacts.contact_email2
FROM (((CLIENT LEFT JOIN AED_MASTER ON AED_MASTER.ACT_NO=CLIENT.ACT_NO)
LEFT JOIN MODELS ON AED_MASTER.MODEL_ID=MODELS.MODEL_ID)
INNER JOIN CAT_SUB ON CLIENT.CATSUB_ID=CAT_SUB.CATSUB_ID)
LEFT JOIN Contacts ON Client.ACT_no=Contacts.ACT_no
WHERE ((Client.ACT_no) = 5132)
GROUP BY Client.ACT_no, Client.Client, Client.Address, Client.Address2, Client.City, Client.State, Client.Zipplus, Client.County, Client.Area_Phone, Client.CatSub_ID, Client.Contact_no,
Cat_Sub.Category, Contacts.contact_email1,  Contacts.contact_email2;

hth
Marcelle
0
 
LZ1Author Commented:
Thank you paisleym.  I do appreciate your patience and help with this issue.  I will be implementing the group by tonight.  I will post my results once I have finished implementing this code.
0
 
LZ1Author Commented:
I am still getting multiple results for one single client.  Maybe the entire SQL needs to be rewritten for me to get the results I'm after.  I have this SQL from a different page that gives me exactly the results I'm after.
SELECT DISTINCT (AED_Master.ACT_no) AS Expr1, Client.ACT_no, * FROM (((Contacts RIGHT JOIN Client ON Contacts.ACT_No=Client.ACT_no)  LEFT JOIN AED_master ON Client.ACT_no=AED_master.ACT_No)   LEFT JOIN Models ON AED_master.Model_ID=Models.Model_ID) LEFT JOIN Cat_Sub ON Client.CatSub_id = Cat_Sub.CatSub_id WHERE (((Client.ACT_no)=?))
I know I have the distinct in there, but i'm wondering if that's what I am needing in order to get the results I'm after.
Your suggested SQL is below.  

SELECT Client.ACT_no, Client.Client, Client.Address, Client.Address2, Client.City, Client.State, Client.Zipplus, Client.County, Client.Area_Phone, Client.CatSub_ID, Client.Contact_no,
Cat_Sub.Category, Contacts.contact_email1,  Contacts.contact_email2,
AED_Master.ACT_no, AED_Master.Serial_Code, AED_Master.Model_ID, AED_Master.Adult_pads, AED_Master.Battery, AED_Master.Ped_Pads, AED_Master.Spare_Adult_Pads, AED_Master.Spare_Battery, AED_Master.Spare_Ped_Pads, AED_Master.Placement, 
Models.Model_ID, Models.Model
FROM (((CLIENT LEFT JOIN AED_MASTER ON AED_MASTER.ACT_NO=CLIENT.ACT_NO)
LEFT JOIN MODELS ON AED_MASTER.MODEL_ID=MODELS.MODEL_ID)
INNER JOIN CAT_SUB ON CLIENT.CATSUB_ID=CAT_SUB.CATSUB_ID)
LEFT JOIN Contacts ON Client.ACT_no=Contacts.ACT_no
WHERE ((Client.ACT_no) = 5132)
GROUP BY Client.ACT_no, Client.Client, Client.Address, Client.Address2, Client.City, Client.State, Client.Zipplus, Client.County, Client.Area_Phone, Client.CatSub_ID, Client.Contact_no,
Cat_Sub.Category, Contacts.contact_email1,  Contacts.contact_email2, AED_Master.ACT_no, AED_Master.Serial_Code, AED_Master.Model_ID, AED_Master.Adult_pads, AED_Master.Battery, AED_Master.Ped_Pads, AED_Master.Spare_Adult_Pads, AED_Master.Spare_Battery, AED_Master.Spare_Ped_Pads, AED_Master.Placement, 
Models.Model_ID, Models.Model;

Open in new window

0
 
LZ1Author Commented:
I do see what your saying.  I will have to use a minimalist approach when writing this.  I will start with just the bare basics and go from there.  When I start getting more than what I need, I'll know where the problem lies.  
Thanks again fro all your help paisleym.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.