StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First off, it seems to me noboody is actually answering your question, which was:</p> <blockquote> <p>What would the query or queries look like to produce such a structure?</p> </blockquote> <p>with a requested structure of </p> <p>Topic, LastModified, # Replies.</p> <p>The SQL to produce a result table with that structure, given the table structures you provided, would be:</p> <pre><code>SELECT t.Id, t.Name AS Topic, MAX(r.Date) AS LastModified, COUNT(*) AS NumReplies FROM Forum_Topic t LEFT OUTER JOIN Forum_Reply r ON t.id = r.topic_id GROUP BY t.Id, t.Name </code></pre> <p>(sorry, this is tested only on SQL Server, as I don't have access to MySql at the moment)</p> <p>Also, your structure <strong>IS</strong> already normalized. Suggestions to the contrary are making assumptions about what you want to do, e.g., assuming that you are interested in tracking user names in <em>addition</em> to email addresses. This is quite reasonable, but is nevertheless an assumption. There is nothing wrong, from a normalization perspective, with using email address as a unique user identifier.</p> <p>Now, if you are looking for general suggestions on how to set up a database, we can give you LOTS of those. Before normalization, I would start with not using potential keywords as object names (e.g., don't give columns names like 'Name' and 'Date').</p> <p>Regarding the comment from Matt about the value being NULL when there are no replies: using the COALESCE() function will fix that. COALESCE() returns the first non-NULL argument (or NULL if all arguments are NULL). So replace the MAX(r.Date) with MAX(COALESCE(r.Date, t.Date)).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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