Solved

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

Posted on 2009-04-06
6
141 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:LZ1
  • 3
  • 2
6 Comments
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24081996
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
 
LVL 28

Accepted Solution

by:
Bill Bach earned 250 total points
ID: 24082020
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
 
LVL 30

Author Comment

by:LZ1
ID: 24082061
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Assisted Solution

by:Wayne Barron
Wayne Barron earned 250 total points
ID: 24082073
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
 
LVL 30

Author Closing Comment

by:LZ1
ID: 31567258
Thanks guys!!!!
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24082131
Glad that we could help.
Carrzkiss

Author Comments:
Thanks guys!!!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question