Query question with 3 FKs

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( 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.
 >> Stay informed about: Query question with 3 FKs 

kpg (ipost@thereforeiam) writes:
> 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.

SELECT n.Note, cre.Name, ass.Name, clo.name
FROM notes n
JOIN users cre ON n.CreatedBy = u.ID
JOIN users ass ON n.AssignedTo = u.ID
JOIN users clo ON n.ClosedBy = u.ID

--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Query question with 3 FKs 

Erland Sommarskog wrote in


> SELECT n.Note, cre.Name, ass.Name, clo.name
> FROM notes n
> JOIN users cre ON n.CreatedBy = u.ID
> JOIN users ass ON n.AssignedTo = u.ID
> JOIN users clo ON n.ClosedBy = u.ID


Got it. Used LEFT OUTER JOINS to handle nulls. Thanks all.
 >> Stay informed about: Query question with 3 FKs