Jump to content

[SOLVED] Combining (nesting?) multiple queries into one


techtheatre

Recommended Posts

i have data located in three tables that i need to pull out to generate a report.  Unfortunately, there is no common element between each table, so a normal JOIN will not work.  Currently my script WORKS, however it relies on one query executing successfully followed by another and then a third.  Is there some way to combine all this into one query or some other way to make it easier to read and manage?  Modifying my table structure is not an option at the moment.

 

My eventual values that need to be populated are:

$CustomerName

$CustomerEmailAddress

$CartContentSummary

 

 

$sql = "SELECT CustomerId, BillToId, CartContentSummary FROM InvoiceData WHERE InvoiceId='$InvoiceId' LIMIT 1";
//echo $sql;
$result = mysql_query($sql, $db);
while($row = mysql_fetch_array($result))
{
   $CartContentSummary = $row['CartContentSummary'];
   $BillToId = $row['BillToId'];
   $CustomerId = $row['CustomerId'];
   
   $sql = "SELECT Email FROM CustomerAccounts WHERE CustomerId='$CustomerId' LIMIT 1";
   //echo $sql;
   $result = mysql_query($sql, $db);
   while($row = mysql_fetch_array($result))
   {
      $CustomerEmailAddress = $row['Email'];
      
      $sql = "SELECT FirstName, LastName FROM Addresses WHERE AddressId='$BillToId' LIMIT 1";
      //echo $sql;
      $result = mysql_query($sql, $db);
      while($row = mysql_fetch_array($result))
      {
         $CustomerName = $row['FirstName']." ".$row['LastName'];

         // if we get this far we have been successful !
      }
   }
}

 

THANKS!  ;D

Link to comment
Share on other sites

Unfortunately, there is no common element between each table,

 

If that were the case you couldn't match any of the data at all! But, the use of LIMIT 1 at the end of each query has me a little perplexed as to what kind of meaningful report you could generate with that.

 

Anyway, this should work:

 

SELECT ID.CustomerId, ID.BillToId, ID.CartContentSummary,
       CA.Email, ADDR.FirstName, ADDR.LastName

FROM InvoiceData ID
    LEFT JOIN CustomerAccounts CA
        ON ID.CustomerId = CA.CustomerId
    LEFT JOIN Addresses ADDR
        ON ID.BillToId = ADDR.AddressId

WHERE InvoiceId='$InvoiceId'

LIMIT 1

Link to comment
Share on other sites

Still looks like joins to me.

 

Um, yeah. Which is exactly why I said his statement about there not being a common element between the tables was false. I think the OP was thinking there had to be "the same" common element between all the tables, and that is not the case.

Link to comment
Share on other sites

Still looks like joins to me.

 

Um, yeah. Which is exactly why I said his statement about there not being a common element between the tables was false. I think the OP was thinking there had to be "the same" common element between all the tables, and that is not the case.

 

Please excuse me, my comment was meant for the original poster and meant no offense.

Link to comment
Share on other sites

Thanks for your help.  i did not know i could do JOINs using multiple variables like that (i thought the same element had to be in all tables of the JOIN).  The reason that i am limiting everything to 1 result is that this query is really just piecing together the necessary elements to send an email to the customer...including their name, address, and recent transaction summary.  I only need the results for this one order, so this is why i put LIMIT 1.  Thanks!

Link to comment
Share on other sites

Still looks like joins to me.

 

Um, yeah. Which is exactly why I said his statement about there not being a common element between the tables was false. I think the OP was thinking there had to be "the same" common element between all the tables, and that is not the case.

 

Please excuse me, my comment was meant for the original poster and meant no offense.

 

No offense taken. It is really difficult to interpret subtle meanings with written text. I wasn't sure if you were being sarchastic to the OP (i.e. "look it really can be done with Joins") or if you were stating that I did not offer the solution the OP had asked for.

 

@techtheatre,

 

Glad to help. But, regarding the LIMIT 1 usage: If invoiceID is a unique field it is not necessary. In fact, in my opinion, I would say it is generally bad practice to use LIMIT 1 in situations when you are looking for a unique record. For instance I see many login scripts where a query uses LIMIT 1 when trying to query for the user's data. I prefer to grab all records that match the query even if I am certain that there is only 1 record. I then will check the results to ensure there is only one record. If there are more than 1 records when there should only be 1 then I can perform the proper error handling.

 

This will help to find errors in my code and to thwart some malicious hacks. However, I'm obsessive about validation.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.