Ben
Ben

Reputation: 687

mySQL : merge data from 3 tables in one result with one query statement

To merge invoice- and corresponding client-adress data from an invoice and a clients table in one query I use this statement :

SELECT * 
FROM invoice, clients 
WHERE invoice.client_id = clients.ID

It works perfect. But now I have a third table coming into the game where invoice_items are stored. Each invoice has one or more items for whom the client will be charged. And each invoice_item UPDATE stores the corresponding, previous generated, invoice.ID. But how to merge 3 tables in one query ? I tried it like this :

SELECT * 
FROM invoice, invoice_item, clients 
WHERE inv_num =:num 
AND invoice.client_id = clients.ID  
AND invoice_item.inv_id = invoice.ID

But I have no success so far. What do I do wrong ?

Any help is appreciated.

EDIT : The whole statement looks like this :

$query = $this->db_con->prepare('SELECT * FROM invoice, invoice_item, clients  WHERE inv_num =:num AND invoice.client_id = clients.ID AND invoice_item.inv_id = invoice.ID');
$query->bindValue(':num',$val, PDO::PARAM_STR);
$success = $query->execute();

$val is the invoice number selected previously from a table.

UPDATE :

With the answer from Stivan I get following result schema. Assume There is 1 invoice with 2 invoice_item's :

    Array {
     [0] => Array {
            // col from table `invoice`
            [col 1]
            [col 2]
            [col n] 
            // col from table `invoice_item`
            [col 1]
            [col 2]
            [col n] 
            // col from table `clients`
            [col 1]
            [col 2]
            [col n] 
      }
     [1] => Array {
            // col from table `invoice`
            [col 1]
            [col 2]
            [col n] 
            // col from table `invoice_item`
            [col 1]
            [col 2]
            [col n] 
            // col from table `clients`
            [col 1]
            [col 2]
            [col n] 
      }
   }

In other words within each array I get all columns from the participating tables with redundant content in table invoice and clients.

How to delimit the columns from clients to 1 col only ?

Or even better saving recources and recieve only 1 array with * from invoice and attached items from invoice_items, so it may looks like this :

Array {
 [0] => Array {
        // col from table `invoice`
        [col 1]
        [col 2]
        [col n] 
        // col from table `clients`
        [col 1]
   }
         Array {
            [0] => Array {
                   // col from table `invoice_items`
                   [col 1]
                   [col 2]
                   [col n] 
                  }
            [1] => Array {
                   // col from table `invoice_items`
                   [col 1]
                   [col 2]
                   [col n] 
                  }
          }
 }

Upvotes: 1

Views: 75

Answers (1)

Stivan
Stivan

Reputation: 1127

select
     *
from invoice
     left join invoice_item on invoice_item.inv_id = invoice.ID
     left join clients on clients.ID = invoice.client_id
where
     invoice.inv_num = :num;  //Or the table where inv_number comes from

 corrected invoice.inv_number to invoice.inv_num

Upvotes: 1

Related Questions