MySQL nested query: returning single row and multiple row queries from two tables

I have two tables: invoice and charges, with a one-to-many relationship. (simplified) invoice has: id, description, date as fields and charges has: id, id_invoice, price, qty, date as fields

For invoice generation I need the info from the invoice table where e.g. id=1 and also all the info from the charges table where id_invoice=1 (so one row from invoice and multiple rows from charges)

At the moment I've got two separate queries but I was wondering if it was possible to do this i开发者_StackOverflow社区n a single query?

Thanks

You could just do a simple JOIN between the two tables joining on invoice.id=charges.id_invoice

The invoice fields would be returned identically for every charge on the same invoice but it'd allow you to fetch the data in a single query.

The query would look something like:

SELECT * FROM invoice i, charges c WHERE i.id=c.id_invoice ORDER BY i.id;

For your needs, a LEFT JOIN seems better. Ex:

SELECT * FROM invoice i LEFT JOIN charges c ON i.id=c.id_invoice ORDER BY i.id;

A nice illustration of SQL joins can be found here