What query will work for this sub-type / super-type relationship?

Question

Here is the situation. I have 3 tables, one super type, and two sub types, with a relationship between the sub types:

|----------------|    |-------------------| |-------------------|
|      Post      |    |     Top_Level     | |      Comment      |   
|----------------|    |-------------------| |-------------------|   
| PK | ID        |    | PK, FK | Post_ID  | | PK, FK | Post_ID  |   
|    | DATE      |    |        | Title    | |     FK | TopLv_ID |   
|    | Text      |    |-------------------| |-------------------|   
|----------------|                                                  

Each post, either comment or top_lev, is unique, but the entities share some attributes. So, comment and top_lev are sub types of post. That is one portion. Additionally, comments are associated with a top_lev post. This ER diagram illustrates this: http://img11.imageshack.us/img11/9327/sampleer.png

What I am looking for is a list of Top_Level posts ordered by activity on that top_level post, either creation of the top_level post, or a comment on that post.

For example, assume we have the following data:

|------------------------|    |------------------|    |--------------------|
|      Post              |    |     Top_Level    |    |       Comment      |
|------------------------|    |------------------|    |--------------------|
| ID |    DATE    | Text |    | Post_ID  | Title |    | Post_ID  |TopLv_ID |
|----|------------|------|    |----------|-------|    |----------|---------|
|  1 | 13/03/2008 | shy  |    |     1    |  XYZ  |    |     2    |    1    |
|  2 | 14/03/2008 | mrj  |    |     3    |  ABC  |    |     4    |    1    |
|  3 | 15/03/2008 | quw  |    |     7    |  NMO  |    |     5    |    3    |
|  4 | 16/03/2008 | ksi  |    |------------------|    |     6    |    1    |
|  5 | 17/03/2008 | kso  |                            |--------------------|
|  6 | 18/03/2008 | aoo  |                            
|  7 | 19/03/2008 | all  |                            
|------------------------|     

|--------------------------------|
|            RESULT              |
|--------------------------------|
| ID |    DATE    | Title | Text |
|----|------------|-------|------|
|  7 | 19/03/2008 |  123  | all  |
|  1 | 13/03/2008 |  ABC  | shy  |
|  3 | 15/03/2008 |  XYZ  | quw  |
|--------------------------------|

Can this be done with a single select statement? If so, how?

Answer 1

I tried this query and it gives the output you describe:

SELECT pt.id, pt.`date`, t.title, pt.`text`
FROM Top_Level t INNER JOIN Post pt ON (t.post_id = pt.id)
 LEFT OUTER JOIN (Comment c INNER JOIN Post pc ON (c.post_id = pc.id))
   ON (c.toplv_id = t.post_id)
GROUP BY pt.id
ORDER BY MAX(GREATEST(pt.`date`, pc.`date`)) ASC;

Output:

+----+------------+-------+------+
| id | date       | title | text |
+----+------------+-------+------+
|  7 | 2008-03-19 | NMO   | all  | 
|  1 | 2008-03-13 | XYZ   | shy  | 
|  3 | 2008-03-15 | ABC   | quw  | 
+----+------------+-------+------+

Answer 2

You've perhaps unintentionally pretty thoroughly obfuscated your question, making it very difficult to understand and answer (at least for those of us with small brains.) Could you possibly restate it with reasonable table and field names, and a more complete description of indexes?

---

EDIT:

Are you possibly describing a situation where products are sold, and also sometimes the same products can be included as components in other products recursively? If so, there are more conventional successful ways to model the situation.

Answer 3

You didn't suggest or hint that the schema could be re-designed, so perhaps my asking this question isn't the best use of your time and mine, but: do your tables have to be designed this way? Is this application already in production use?

What triggered this thought in my head was your statement:

All events happen at a date, but edits must be associated with the original creation event

I suspect that this is the rationale for having sub2 include a FK to sub1. But this feels to me like a denormalization (in relational terms) or a violation of the DRY principle (in Agile terms.) In either case, it may make your life more complicated than it needs to be.

My advice (worth what you're paying for it) would be to remove the FK to sub1. In its place, you can either 1) include a SELECT on the creation event for the page, as part of the query, or 2) Move the creation date into the page table. (Which of these options you take depends on how much additional complexity exists in the schema; I'd usually prefer #1 over #2.)

I would think that this would simplify your updates as well as your queries. Your mileage may vary.

Answer 4

That should definitely be possible, as long as "date of the last occurrence" is represented in a column of Sub1 and Sub2. Note that the code I'm listing is mostly pseudocode... you'll have to fill in the blanks according to your flavor of dbms.

To limit your result-set to only the columns in SUPER and SUB1 (with no dupes):

SELECT DISTINCT sub1.*, super.*

(obviously, you should try to avoid SELECT *... pick which columns you actually need)

You FROM clause would look something like:

FROM super INNER JOIN sub1 ON super_id
    LEFT JOIN sub2 ON super_id AND sub1_id

And ORDER BY would use the COALESCE function (if your dbms supports it) to figure out which column to sort by (COALESCE chooses the first non-null value in the argument list):

ORDER BY COALESCE(sub2.dateColumn, sub1.dateColumn)

Alternatively, if you don't have COALESCE available but you do have an ISNULL function available, you can chain ISNULLs:

ORDER BY ISNULL(firstDateColumn, ISNULL(secondDateColumn, thirdDateColumn))