Mibble Posted January 20, 2010 Share Posted 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"; Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/ Share on other sites More sharing options...
ianh Posted January 21, 2010 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/#findComment-999106 Share on other sites More sharing options...
kickstart Posted January 21, 2010 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/#findComment-999281 Share on other sites More sharing options...
Mibble Posted January 22, 2010 Author Share Posted 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); Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/#findComment-999927 Share on other sites More sharing options...
kickstart Posted January 22, 2010 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/#findComment-999937 Share on other sites More sharing options...
Mibble Posted January 22, 2010 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/#findComment-999941 Share on other sites More sharing options...
kickstart Posted January 22, 2010 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/#findComment-999950 Share on other sites More sharing options...
Mibble Posted January 22, 2010 Author Share Posted 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> Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/#findComment-999953 Share on other sites More sharing options...
Mibble Posted January 22, 2010 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/#findComment-999956 Share on other sites More sharing options...
kickstart Posted January 22, 2010 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/#findComment-999971 Share on other sites More sharing options...
Mibble Posted January 22, 2010 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/#findComment-1000157 Share on other sites More sharing options...
kickstart Posted January 23, 2010 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/#findComment-1000374 Share on other sites More sharing options...
Mibble Posted January 26, 2010 Author Share Posted January 26, 2010 i do have a workbench version with the db and relationships however do not know how to post that here. Quote Link to comment https://forums.phpfreaks.com/topic/189237-wanting-to-do-join-so-i-can-use-to-send-email/#findComment-1002097 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.