Reputation: 687
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
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