How to pull just one variable from database while keeping everything else

Hey Experts!!
I am a little rusty on my SQL writing and hope someone can help me.

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 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.Client)) LIKE ?;

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.
LVL 30
LZ1Asked:
Who is Participating?
 
Bill BachConnect With a Mentor PresidentCommented:
Use two iterative loops.  

In the outer loop, use your same query, but change "SELECT Client.ACT_no" to "SELECT DISTINCT(Client.ACT_no)".  This will give you a data set that includes all of the clients to whom an Email is needed.

Then, on the INSIDE of the outer loop, run the query as you have given it, but for a single client only (add a WHERE clause for the Client.ACT_no field to restrict to a single client) and build the Email including all of the products in a short table or list.
0
 
Wayne BarronAuthor, Web DeveloperCommented:
yep.
Just have all the products listed on 1 page.
Doing a LOOP.
Then You can send the email out with all the products listed with the LOOP.

You can have the products to display on the page via a Text Field.
And then grab all the contents from the text field
Adding a Break between each....

This sound like something that would be easy to accomplish.

Carrzkiss
0
 
LZ1Author Commented:
How about this?  What if I want to do a list that shows the 1 e-mail address and the 80 different products?  Same thing??????
I am using Dreamweaver as well.............
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!

 
Wayne BarronConnect With a Mentor Author, Web DeveloperCommented:
Then you will need to do a Relationship between the Table with the Email Addres
And the Table with the Products.
If the Table with the Email has an ID and the Table with the Products has an ID
And these ID's are the same between the 2 tables.
Then Create a Relationship between the 2 tables.
0
 
LZ1Author Commented:
Thanks guys!!!!
0
 
Wayne BarronAuthor, Web DeveloperCommented:
Glad that we could help.
Carrzkiss

Author Comments:
Thanks guys!!!!
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.