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

[techtheatre]

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! 

[Psycho]

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

[bubblegum.anarchy]

Still looks like joins to me.

[Psycho]

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.

[bubblegum.anarchy]

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.

[techtheatre]

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!

[Psycho]

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.