One to many too much data returned - MySQL

Question

I have 2 related MySQL tables in a one to many relationship.

Customers: cust_id, cust_name, cust_notes

Orders: order_id, cust_id, order_comments

So, if I do a standard join to get all customers and their orders via PHP, I return something like:

• Jack Black, jack's notes, comments about jack's 1st order
• Jack Black, jack's notes, comments about jack's 2nd order
• Simon Smith, simon's notes, comments about simon's 1st order
• Simon Smith, simon's notes, comments about simon's 2nd order

The problem is that *cust_notes* is a text field and can be quite large (a couple of thousand words). So, it seems like returning that field for every order is inneficient.

I could use *GROUP_CONCAT* and JOINS to return all *order_comments* on a single row BUT order_comments is a large text field too, so it seems like that could create a problem.

Should I just use two separate queries, one for the customers table and one for the orders table?

Is there a better way?

Answer 1

If i understand your question correctly, it is better to use two query, for a results like:

Jack black
   comments
   comments
   comments
   comments

Simon Smith
   comments
   comments
   comments

Answer 2

Typically we used a few different strategies.

In one case we returned multiple result sets - one set for the parents and one for all the children. The ORDER BY was the same, so that you could easily interleave the children for each parent in the client code.

It was also possible to return multiple result sets - one for the parents and one set of children for EACH parent (we used a cursor for the outer part of this).

Another thing we used (in SQL Server) was XML so it actually returned the hierarchical form.

Answer 3

You can run two queries, one for notes and one for the customer details.

// this will give you orders in an efficient way
SELECT cust_id, order_id, order_comments
FROM customers AS c
LEFT JOIN orders as o ON o.cust_id = c.cust_id
LIMIT xx, yy

// this will get all details from N specific customers
SELECT * FROM customers
WHERE cust_id = 'xxx' OR cust_id = 'yyy' OR cust_id = 'zzz'

And then pass the second query to an array and, as you're showing the first one, use the details from the customer that are stored in the array.

Should be pretty efficient.

Answer 4

Look. Your page is much like an question page on SO. A question first and answers below.
Noone bother to request it with one query!

Answers - yes, uses join, but to get names and id's only, not full user's info.
Nothing complicated.

Just use separate queries for the customer and for the list of orders