wanting to do join so i can use to send email

Mibble · January 20, 2010

hi there,

i have three tables which i need info from, none of which have a common field.

the sql queries work individually, how do i combine them to get what i need?

SELECT Client_Organization, logo from CLIENTS;

SELECT eventno, eventname, eventdescr, client, eventaddress, eventcity, eventstate, eventcountry, date1, dateend, icon from EVENTS;

SELECT DISTINCT Account_Username, Contact_FirstName, Contact_Email from REGISTRANTS WHERE ContactEmail_Request = "YES";

ianh · January 21, 2010

I'm no expert, but to do a join you need to have common field(s).

If your want to put the data in an email (I presume with PHP or other language) you can just output and process the queries individually.

kickstart · January 21, 2010

Hi

As above. To do the JOINs you need to decide on which fields are used to connect the data.

All the best

Keith

Mibble · January 22, 2010

Hi all,

Well after much going back and forth with the designer he finally told me which fields are tied into the other (he kept telling me t here were no relationships).

Now I have my query, however due to the amount of records in the database, unless I do a loop of some sort the temp file which will be created by the query will be astronomical. This is for another post altogether.

This ended up being what the query looked like. I just need to clean it up and loop my mailer query loop now.

Thanks everyone.

select events.eventno, events.eventname, events.eventname2, events.eventdescr, events.date1, events.dateend ,events.client, events.eventaddress, events.eventcity, events.eventstate, events.eventzip, events.eventcountry, clients.Client_Organization, clients.logo, products.category, registrants.User_Number, registrants.ContactEmail_Request = "YES", eventregistration.RegistrantID from events, clients, products, registrants, eventregistration WHERE (events.clientID_Clienttable = clients.Client_Number AND eventregistration.RegistrantID = registrants.User_number);

kickstart · January 22, 2010

Hi

You are only joining events with clients and eventregistration with registrants. For the rest of them you are effectively doing a cross join.

For example you code will give you every combination of events and eventregistration.

All the best

Keith

Mibble · January 22, 2010

sorry i am at home and remembering what i did at the office. i believe i changed the beginning to a distinct:

registrants.User_Number, registrants.ContactEmail_Request = "YES" and then the rest of the query.

kickstart · January 22, 2010

Hi

Don't think the distinct would help much.

Say joining events with clients brings back 1000 distinct records, that there are 1000 distinct records on products and eventregistration with registrants brings back 1000 distinct records then in total you will get back 1,000,000,000 rows

All the best

Keith

Mibble · January 22, 2010

wow, that many. any suggestions on what i should be able to cut it down?

this is the code for the entry which will be used for the emails.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>HTML & TEXT Entry for email</title>
</head>
<body>
<?php
if (!isset($_POST['submitForm'])) {
?>
<form id="ukbidders" name="ukbidmsg" action="process1.php" method="post">
  <strong>All Overseas Bidders</strong><br />
  <table>
    <tr>
      <td>HTML Message:<br />
        <textarea name='message_html' cols="50%" rows="10">
                        </textarea>
      </td>
      <td>TEXT Message:<br />
        <textarea name='textarea' cols="50%" rows="10">
                </textarea></td>
    </tr>
    <td> </td>
      <td align="right"><input type="submit" name="SubmitForm" value="Submit">
        <INPUT type="reset">
      </td>
  </table>
</form>
<?php

        } else {
        echo "Form Submitted!";
        }
?>
<?php
        if (!isset($_POST['submitForm'])) {
?>
<!--  Display BidSpotter.com Form  -->
<form id="allbidspot" name="allbidspotmst" action="process2.php" method="post">
  <strong>All US Bidders</strong>
  <table>
    <tr>
      <td>HTML Message:<br />
        <textarea name="message_html" cols="50%" rows="10">
                        </textarea>
      </td>
      <td>TEXT Message:<br />
        <textarea name="message_nohtml" cols="50%" rows="10">
                </textarea>
      </td>
    </tr>
    <td> </td>
      <td align="right"><input type="submit" name="SubmitForm" value="Submit">
        <INPUT type="Reset">
      </td>
  </table>
</form>
<?php

        } else {
        echo "Form Submitted!";
        }
?>
<?php
        if (!isset($_POST['submitForm'])) {
?>
<form id="categorized" name="category_sel" method="post" action="process3.php">
  <!--  Display Categorized Form  -->
  <strong>Categorized Bidders:</strong><br />
  <label>Categories
  <select name="select">
    <option value=0>0-Construction Equipment</option>
    <br />
    <option value=1>1-Consumer-Antiques/Collectibles/Memorabilia</option>
    <br />
    <option value=2>2-Industrial/Commercial-Machinery</option>
    <br />
    <option value=3>3-Real Estate</option>
    <br />
    <option value=4>4-High Tech-Computers-Lab EQ</option>
    <br />
    <option value=5>5-Auto/Vehicle</option>
    <br />
    <option value=6>6-Oil & Gas Properties</option>
    <br />
    <option value=7>7-Cattle & Horses</option>
    <br />
    <option value=8>8-Agricultural</option>
    <br />
    <option value=9>9-Yachts/Boats</option>
    <br />
  </select>
  </label>
  <br />
  <table>
    <tr>
      <td>HTML Message:<br />
        <textarea name="message_html" cols="50%" rows="12">
                        </textarea>
      </td>
      <td>TEXT Message:<br />
        <textarea name="message_nohtml" cols="50%" rows="12">
                        </textarea>
      </td>
    </tr>
    <td> </td>
      <td align="right"><input type="submit" name="SubmitForm" value="Submit">
        <INPUT type="Reset">
      </td>
    </tr>
  </table>
</form>
<?php
}
?>
</body>
</html>

Mibble · January 22, 2010

here is a bit more info on what the request stated:

The tool should correctly identify an event from the BidSpotter event lists based on the entry of the BidSpotter.com event ID #.

e.g. http://www.site.com/forms/event.php?event=9443

The tool should accept entry of a .co.uk event id# to associate into a link to the event details page on site1 .co.uk

e.g. https://site.site1.com/forms/SaleDetails.php?eventid=138

It should pull Client name and logo, date, location and title info associated with that site.com event into a form letter.

e.g.

Chocolate Manufacturing and Packaging Machinery, Ice Cream & Patisserie Machinery, Etc.

starts 11 January 2010 and concludes 19 January 2010

(please not that the formatting of the date is important for cultural reasons)

The email should be targetable to a mailing list of any of the following sub-sets of the sites registered users:

e.g. european registered Bidders

All us users

Categorized users as currently available on the site but not automatically associated with a category but instead selectable each time.

The email should include, as now, a message for inputting additional text. Ideally all test would be editable.

kickstart · January 22, 2010

Hi

Afraid I would need details of the various tables to give a real answer.

However what you need is to full specify how the tables join together. eg, I suspect that events is link to eventregistration by an event id. Also suspect that events and clients are linked by the registrants table.

No idea how you need to bring the product table into it. You appear to just be bringing in the category field from the products table but without specifying. Possible that you need a list of products, but can't see how you intend to limit that list (do product categories refer to clients or events, or both?).

All the best

Keith

Mibble · January 22, 2010

this is from the form above:

<form id="categorized" name="category_sel" method="post" action="process3.php">
  <!--  Display Categorized Form  -->
  <strong>Categorized Bidders:</strong><br />
  <label>Categories
  <select name="select">
    <option value=0>0-Construction Equipment</option>
    <br />
    <option value=1>1-Consumer-Antiques/Collectibles/Memorabilia</option>
    <br />
    <option value=2>2-Industrial/Commercial-Machinery</option>
    <br />
    <option value=3>3-Real Estate</option>
    <br />
    <option value=4>4-High Tech-Computers-Lab EQ</option>
    <br />
    <option value=5>5-Auto/Vehicle</option>
    <br />
    <option value=6>6-Oil & Gas Properties</option>
    <br />
    <option value=7>7-Cattle & Horses</option>
    <br />
    <option value=8>8-Agricultural</option>
    <br />
    <option value=9>9-Yachts/Boats</option>
    <br />
  </select>
  </label>
  <br />
  <table>
    <tr>
      <td>HTML Message:<br />
        <textarea name="message_html" cols="50%" rows="12">
                        </textarea>
      </td>
      <td>TEXT Message:<br />
        <textarea name="message_nohtml" cols="50%" rows="12">
                        </textarea>
      </td>
    </tr>
    <td> </td>
      <td align="right"><input type="submit" name="SubmitForm" value="Submit">
        <INPUT type="Reset">
      </td>
    </tr>
  </table>
</form>

and from part of the query

WHERE (events.clientID_Clienttable = clients.Client_Number AND eventregistration.RegistrantID = registrants.User_number);

i do have the database. for the products, just the categories is coming in based upon their selection which products.category, comes from. i can get the db skeleton, which is all i have been given.

it has been awhile since i did this, so i am a bit slow now.

kickstart · January 23, 2010

Hi

Problem is that you query is effectively 3 seperate queries:-

events joined with clients

products

registrants joined with eventregistration

Somehow you need to join those 3 together. You selection list might be used to limit one of those queries, but you will still get every combination of the other 2 as you have specified nothing between them as a field.

What you have is getting a list of every event and client where clientID_Clienttable = Client_Number. Then for every single one of those you get all the products. And then for every single one of those you get every registrants joined with eventregistration where RegistrantID = User_number.

While I am fairly sure you can limit this, without knowing what the tables contain and what the fields mean I am afraid I am very limited in what I can suggest.

All the best

Keith

Mibble · January 26, 2010

i do have a workbench version with the db and relationships however do not know how to post that here.

Sign In

wanting to do join so i can use to send email

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information