Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Your two questions here. One is how to most efficiently gather the data, the other is how to deal with the data you receive. </p> <p>First how to gather it:</p> <pre><code>SELECT a.* FROM Author a INNER JOIN Book b ON a.id=b.author_id WHERE left(a.name,1)='a' </code></pre> <p>Like is very expensive, avoid it if you can. Inner joins are your least expensive way to compare tables - especially if you have indexes on the Primary keys (id) and Foreign keys(author_id) If there are authors without books (wouldn't be much of an author I guess), use a LEFT JOIN (same syntax just change 'inner' to 'left')</p> <p>Second. If you are talking about 1000s of records you might want to think of gathering the data in two separate pulls (like your first choice I think) unless you want the user to wait forever for the page to load. For example: the user chooses all authors that begin with a and receives a list back - you could even have a count of the articles from that author.</p> <pre><code>SELECT a.Name count(b.author_id) titles FROM Author a INNER JOIN Book b ON a.id=b.author_id WHERE left(a.name,1)='a' GROUP BY a.Name </code></pre> <p>They'd see</p> <pre><code>John Adam: 35 Titles Jane Acaba: 18 Titles Jim Allan: 3 Titles </code></pre> <p>Then the user clicks on the author, load the list of books for that author. </p> <pre><code>Jim Allan's Titles: Froggy went a court'n Death on the Nile Life in Africa </code></pre> <p>If you want it to appear like it's all done with one pull, but you want it to come over really fast use xmlhttp or ajax to show the list of authors. That is a whole other topic. :)</p> <p>I also should add that stored procedures are a must. The will add significantly to the portability of the app as well as to the performance in most databases. </p> <p>HTH</p>
    singulars
    1. This table or related slice is empty.
    1. POWhat is the best way to handle (nested) record sets?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPraesagus
    1. USPraesagus
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload