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

Question

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 in a single query?

Thanks

Could we see those queries please ?

mcgrailm
– mcgrailm

2011-04-23 23:30:54 +00:00
Commented Apr 23, 2011 at 23:30 — mcgrailm
– mcgrailm, Commented Apr 23, 2011 at 23:30

James C · Accepted Answer · 2011-04-23 23:33:52Z

1

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;

answered Apr 23, 2011 at 23:33

James C

14.2k1 gold badge37 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RolandFlyBoy Over a year ago

Thanks, that makes sense. Would this be more efficient from a DB load perspective than two separate queries?

James C Over a year ago

It's hard to say. There'll be less work for the client (there's only one query instead of two) but there's more overall data which could create more network traffic. I suspect there's very little in it.

Valentin · Accepted Answer · 2011-04-25 15:58:37Z

0

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

answered Apr 25, 2011 at 15:58

Valentin

1,06616 silver badges23 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related