Solved

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

Posted on 2009-04-06
6
142 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Industry Leaders: 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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…

742 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