Solved

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

Posted on 2009-04-06
6
139 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 30

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 30

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 30

Expert Comment

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

Author Comments:
Thanks guys!!!!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

809 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