Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Schema design for reporting system

    I have a database schema that records test results for which the key table fields are:

    Test ID
    Test stage ID
    Timestamp

    This allows me to record results for tests with different numbers or types of stages without requiring a separate table with a different schema for each type of test. For the reporting purposes, I would like to be able to build views on top of this table that collapse all the entries for an individual test into a single row. For example, if I had a particular test type with three different stages, I would like to have a view with these columns:

    Test Id
    Stage 1 timestamp
    Stage 2 timestamp
    Stage 3 timestamp

    that would collect the timestamps from each separate row in the underlying table into a single row. The only thing I could think of was an GROUP BY clause such as the one below, but I cannot select any of the data fields that are not part of the group by clause. Any suggestions on how to do this?

    SELECT a.testId as Id, a.timestamp as Stage1, b.timestamp as Stage2, c.timestamp as Stage3

    FROM Test a, Test b, Test c

    WHERE a.testId=b.testId and b.testId=c.testId and a.stageId=0 and b.stageId=1 and c.stageId=2

    GROUP BY a.testId

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Code:
    SELECT subselect.*
         , otherstuff.*
    FROM   otherstuff
     INNER
      JOIN (
            SELECT id
                 , timestamp
            FROM   otherstuff
            GROUP
                BY id
                 , timestamp
           ) As subselect
        ON otherstuff.id = subselect.id
    ??
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2008
    Posts
    2
    Ah, excellent. That was almost exactly what I needed. If I drop the timestamp from the subselect in your example, it's exactly what I was trying to do. Thanks very much for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •