?
Solved

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

Posted on 2009-04-06
6
Medium Priority
?
145 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 1000 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 31

Assisted Solution

by:Wayne Barron
Wayne Barron earned 1000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 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