How can I create a view that does a joins a table to itself and gives a means to select based on a key value

Question

I have the following table DDL:

CREATE TABLE [dbo].[Test] (
    [UserId]                  NVARCHAR (128) NOT NULL,
    [TestId]                  INT            IDENTITY (1, 1) NOT NULL,
    [ExamId]                  INT            NOT NULL,
    [Title]                   NVARCHAR (100) NULL,
    [Status]                  INT            NOT NULL,
    [CurrentQuestion]         INT            DEFAULT ((999)) NOT NULL,
    CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TestId] ASC)
);

This table contains:

A list of ALL Exams and Tests that are available. These rows have a UserId = 0
A list of Exam and Test that are in progress for a particular user (UserId != 0)
A user may have one or may have multiple rows in this table.

Is there a way that I can create a view that shows a list of all the tests and where it exists the additional information for a user if that user has started a test. I'm really not sure if this needs to be a view or a stored procedure as it would need to take as input a UserId.

For the UserId = 1 who in this example has started two of the available tests:

ExamId  TestId   Title      Status

1       1        Exam1Test1 Started
1       2        Exam1Test2
2       3        Exam1Test3 Started
2       4        Exam1Test4

or for the UserId = 2 who in this example has started three of the available tests:

1       1        Exam1Test1 Started
1       2        Exam1Test2
2       3        Exam1Test3 Started
2       4        Exam1Test4 Started

I am not sure why you need a join, view or procedure. Isn't what you want just a simple select statement of: `Select * FROM Test WHERE UserId = '1'`. This would return all of the current students exams and status. Would you mind explaining more please?...just read more... you might just need to do a left join so that you can also see things that a student is not associated with? — TheOneWhoPrograms, Mar 20 '14 at 12:59
marc_s - Thanks for the edit. Please feel free to change anything you think might make this more readable. In particular the wording of the title. — , Mar 20 '14 at 13:00
TheOneWhoPrograms - The select you mention would give me all the tests started. However I would like to get all the tests available as well as just those started. — , Mar 20 '14 at 13:02
I think this link here may help. http://stackoverflow.com/questions/10334993/sql-left-self-join-with-where-clause-dependencies-between-the-two-copies-of-the the were clause being where you want a specific user. — TheOneWhoPrograms, Mar 20 '14 at 13:03
If `test_id` is the primary key, how can you have multiple users doing a given test at the same time? If you can't, then you don't need a view/join/proc etc. If you can, your schema is faulty. — Bohemian, Mar 20 '14 at 13:04
I will have a look at that now. Hope I can get an example of how to code that into a view or stored procedure. — , Mar 20 '14 at 13:04
@Bohemian - Good point. I will look into that. I think I will need to change my primary key. Thank you very much !!! — , Mar 20 '14 at 13:06
Thing is, (with the tools i've used atleast) a procedure doesn't really give you output, its good for doing operations on a table. You can technically have a temp table to hold what you want. So at begining of procedure you empty it, then populat eit with some data. The problem with a view is it would be restrictive to a single user with no variable input (from what I know), so you don't want a view. — TheOneWhoPrograms, Mar 20 '14 at 13:06
@Melina changing the key isn't the right fix. You need another table, say `user_tests` with columns `user_id, test_id`. Such a table is an *association* table, and is the only (realistic) way of creating a many-to-many relationship in a relational database. — Bohemian, Mar 20 '14 at 13:10

score 0 · Accepted Answer · answered Mar 20 '14 at 13:06

Your schema implies that any given test may be taken by at most one user at a time.

The query that will return the results you want is:

select
  ExamId,
  TestId,
  Title,
  case when userId = ? then "Started" else "" end as Status
from Tests

Of course you would replace the ? in the query with the actual user you're interested in.

How can I create a view that does a joins a table to itself and gives a means to select based on a key value

1 Answers1