Ben Ben - 6 months ago 10
JSON Question

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]
}
}
}

Answer
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
Comments