Prev: SSRS2008 connecting to Oracle
Next: query question
From: kpg on 6 Jul 2010 12:31
Novice Query question:

I have a table with 3 foreign keys, for example:

Notes
ID as int
CreatedBy int ;FK User_table.ID
AssignedTo int ;FK User_table.ID
ClosedBy int ;FK User_table.ID
Note nvarchar(max)

and a talbe of users, for example

Users
ID as int
Name as nvarchar(50)
Initials as nvarchar(3)

I need a query that returns the the notes with the initials of the users,
so I tried this:

SELECT n.*, u.Initials AS uCreatedBy, u.Initials AS uAssignedTo, u.Initials
AS uClosedBy FROm Notes as n, Users as u WHERE n.ID = @id AND n.CreatedBy =
u.ID AND n.AssignedTo = u.ID AND n.ClosedBy = u.ID

I get no records, understandable, sinde no record has all three FK's set to
the same user.

What is the proper way to query this?

thanks,
kpg

From: Scott Morris on 6 Jul 2010 13:16

"kpg" <ipost(a)thereforeiam> wrote in message
news:Xns9DAD755DDF0BFipostthereforeiam(a)207.46.248.16...
> Novice Query question:
>
> I have a table with 3 foreign keys, for example:
>
> Notes
> ID as int
> CreatedBy int ;FK User_table.ID
> AssignedTo int ;FK User_table.ID
> ClosedBy int ;FK User_table.ID
> Note nvarchar(max)
>
> and a talbe of users, for example
>
> Users
> ID as int
> Name as nvarchar(50)
> Initials as nvarchar(3)
>
> I need a query that returns the the notes with the initials of the users,
> so I tried this:
>
> SELECT n.*, u.Initials AS uCreatedBy, u.Initials AS uAssignedTo,
> u.Initials
> AS uClosedBy FROm Notes as n, Users as u WHERE n.ID = @id AND n.CreatedBy
> =
> u.ID AND n.AssignedTo = u.ID AND n.ClosedBy = u.ID
>
> I get no records, understandable, sinde no record has all three FK's set
> to
> the same user.
>
> What is the proper way to query this?

You join three times to the Users table - generating one row per type of
user. You might want to outer join to get the assigned-to and closed-by
users (assuming these might be nullable values). Alternatively, you can use
subqueries to generate 1 row per note:

select CreatedBy,
(select Initials from Users where Users.ID = Notes.CreatedBy) as
CreatedInitials,
....
from Notes

More information can be found in BOL - use the index and look for
"subqueries".


From: --CELKO-- on 6 Jul 2010 20:04
Instead of inventign your own programming language, could you use real
DDL instead? Also, please read ISO-11179 rules -- there is no magical
generic ID data element in RDBMS. Is this what you meant?

CREATE TABLE Notes
(note_id INTEGER NOT NULL PRIMARY KEY,
creator_user_id INTEGER NOT NULL
REFERENCES Users(user_id),
assignee_user_id INTEGER NOT NULL
REFERENCES Users(user_id),
closer_user_id INTEGER NOT NULL
REFERENCES Users(user_id),
note_txt VARCHAR (2000));

CREATE TABLE Users
(user_id AS INTEGER NOT NULL PRIMARY KEY,
user_name VARCHAR(50) NOT NULL,
user_initials VARCHAR(3) NOT NULL);

SQL Server will have problems with cycles in the DRI; other products
do not.

>> I need a query that returns the the notes with the initials of the users ..<<

SELECT N.note_id, U.user_initials
FROM Notes AS N, Users AS U
WHERE N.note_id = @in_note_id
AND U.user_id IN
(N.creator_user_id, N.assignee_user_id, N.closer_user_id);

>> no record [sic: rows are not records] has all three FK's set to the same user. <<

How did you enforce that rule?
CHECK (creator_user_id NOT IN (assignee_user_id, closer_user_id)
CHECK (closer_user_id NOT IN (assignee_user_id, creator_user_id)

Can there be NULLs? This does not work so good with them and the
constraints get harder to write.

You might try a different schema design. Pull the user roles out as an
attribute in a relationship table.

CREATE TABLE Notes -- entity
(note_id INTEGER NOT NULL PRIMARY KEY,
note_txt VARCHAR (2000));

CREATE TABLE Users -- entity
(user_id AS INTEGER NOT NULL PRIMARY KEY,
user_name VARCHAR(50) NOT NULL,
user_initials VARCHAR(3) NOT NULL);

CREATE TABLE Note_Assignments
(note_id INTEGER NOT NULL
REFERENCES Notes(note_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
assignment_type CHAR(8) NOT NULL
CHECK (assignment_type IN ('creator', 'assignee', 'closer')),
PRIMARY KEY (note_id, user_id)
UNIQUE (note_id, user_id, assignment_type);

Look at how the overlapping UNIQUE constraints work to prevent a user
from multiple roles in a note.
 | 
Pages: 1
Prev: SSRS2008 connecting to Oracle
Next: query question