Sbos Posted January 8, 2008 Share Posted January 8, 2008 I have a question about how to do a query the best way possible. Right now our company has an order system which allows you to place orders with us. The main table contains all the information about the order but it also contains tables with information attached to that order as well, for instance, tracking numbers, items ordered. My question is when I do a query to display the order information and also want to have it display the tracking numbers attached to that order how would I go about doing that in a single query? I'm trying to figure out the most efficient way to code this because i forsee having to use this in similar instances. Let me give some examples: the order table has an ID column as well as a bunch of information including order number, billing info, ordered by, etc. the tracking number table has an ID column, tracking number, shipping carrier and an ID for the order it's attached to. I want to make a query that displays a list out of all the orders let's say and in that table I want to display all the tracking numbers that match that order as well. so basically ordernum billto trackingnum D100 Jon 1938319183 1193930102 D101 Tim 1030040313 D102 Bill 1300203000 1393949299 3939293939 Quote Link to comment Share on other sites More sharing options...
fenway Posted January 8, 2008 Share Posted January 8, 2008 Sounds like you need to join the tables... Quote Link to comment Share on other sites More sharing options...
Sbos Posted January 9, 2008 Author Share Posted January 9, 2008 is there anyway to keep it so the duplicate information isn't entered into the subsequent rows? When i join the tables my order information is entered repeatedly for each different tracking number on that order, Ultimately cluttering up the output. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 9, 2008 Share Posted January 9, 2008 Hopefully you're not outputting the recordset directly... Quote Link to comment Share on other sites More sharing options...
Sbos Posted January 9, 2008 Author Share Posted January 9, 2008 When you say that what exactly do you mean? After I get the results from the query I take the results and use a loop to display the columns I want in an HTML table. so an example of a possible output would be formatted like this from the join Order Number Bill To Tracking Number D101 Tom's 100302030 D101 Tom's 102003010 D101 Tom's 103994828 D102 Joe's 100300302 D103 Tim's 100303020 D103 Tim's 100030202 That's just arbitrary but that's how it displays. I'd like to take the data and display it in a fashion more similar to this Order Number Bill To Tracking Number D101 Tom's 100302030 102003010 103994828 D102 Joe's 100300302 D103 Tim's 100303020 100030202 Quote Link to comment Share on other sites More sharing options...
fenway Posted January 9, 2008 Share Posted January 9, 2008 That's a php issue... just keep track of the last order number & bill to values, and don't output anything if they're in the same as the current one; otherwise, update the last with the current, and continue. Quote Link to comment Share on other sites More sharing options...
Sbos Posted January 9, 2008 Author Share Posted January 9, 2008 Thanks for the help, Simple solution for a simple problem apparently, I guess that's why the tag next to my login says n00bie. Quote Link to comment 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.