I face what must be a very common problem, yet don't know the best way to solve it. As an example, let's assume a very simple database listing last names of authors and home addresses for each author. I want to pull this information out of the database and display it on a web page. For many authors, this will be simple: 1 last name is pulled with 1 address. Some sql like
will yield l row of data for each of the authors having 1 address, which can easily be loaded into a Bean and ultimately be displayed via JSP, etc.
But some authors will have more than 1 address, say, for example 2 addresses. In these cases, the example SQL will return as many rows as there are addresses: 2. Use of DISTINCT will not eliminate this problem as long as author.lastName and address.line1, etc. are retrieved in the same query. In this example, 2 rows will be retrieved. But I want to display the author's last name only once, while each of the several addresses is also displayed, perhaps in an indented column.
One easy way of handling this is to create a "flattened table" every night for easy querying and displaying, with fields like "address1", "address2", etc. But such a table, is, ultimately, not flexible.
What's the best way of solving the display of 1-to-many data problem? Dividing the query into 2 queries? a query for author.lastName and a separate query for the address information? Inefficient and quite slow if lots of tables/objects are involved, but I don't see any other way.
I'm not seeing a problem, actually. Add an "ORDER BY author.id" so that all the records for a given author are together. Then use a state variable to store the id of the author after you display each one. Then each time you display a row, check if the id is the same as it was the previous time around the loop; if so, don't display the author's name, just the address.