I am struggling to get a relationship to work in my database and I cannot
get my head round the problem so I wondered if any of you knowledgeable
people on here knew the answer.
An image of my Diagram is here
(http://www.step-online.org.uk/relationship.png) showing some of the
relationships.
Everything works find as is.
I am now trying to create a many-to-many-to-many type of relationship (if
this is even possible).
My courses tables work fine as a many-to-many relationship.
I have created a new join table so I can link individuals to courses (using
the unique ID column in the Courses join table).
All the fields ending _ID are nnumeric data type with an identity set to
increment by 1 each time.
I have tried to create a relationship between XREF_Courses_ID in the
SYS_Xref_Join_Ind_Courses table and the same field (both primary keys) in
the SYS_Xref_Join_Courses table, and receive the following error:
"The columns in table 'SYS_Xref_Join_Courses' do not match an existing
primary key or UNIQUE constraint".
Why would this be?On Tue, 6 Apr 2004 12:22:53 +0100, "Keith" <@..> wrote:
>I am struggling to get a relationship to work in my database and I cannot
>get my head round the problem so I wondered if any of you knowledgeable
>people on here knew the answer.
>An image of my Diagram is here
>(http://www.step-online.org.uk/relationship.png) showing some of the
>relationships.
>Everything works find as is.
>I am now trying to create a many-to-many-to-many type of relationship (if
>this is even possible).
>My courses tables work fine as a many-to-many relationship.
>I have created a new join table so I can link individuals to courses (using
>the unique ID column in the Courses join table).
>All the fields ending _ID are nnumeric data type with an identity set to
>increment by 1 each time.
Aaarrgghhhhhh...
>I have tried to create a relationship between XREF_Courses_ID in the
>SYS_Xref_Join_Ind_Courses table and the same field (both primary keys) in
>the SYS_Xref_Join_Courses table, and receive the following error:
>"The columns in table 'SYS_Xref_Join_Courses' do not match an existing
>primary key or UNIQUE constraint".
>Why would this be?
>
Because the columns in that table do indeed not match an existing
primary key. Both tables hava a primary key over multiple columns, so
you can't use just one of these columns to create a relationship.
Explanation: any foreign key (which is what a relationship translates
to) needs to join on at least one side to a column or column
combination that identifies at most one row. Generally the primary
key; optionally a column combination with UNIQUE constraint (a
candidate key).
A many-to-many relationship can only be implemented through an extra
table. For the relationship between courses and individuals, something
like:
CREATE TABLE CourseSubscriptions
(IND_ID int not null,
COURSE_ID int not null,
PRIMARY KEY(IND_ID, COURSE_ID),
FOREIGN KEY(INT_ID)
references SYS_Individual(INT_ID),
FOREIGN KEY(COURSE_ID)
references SYS_Courses(COURSE_ID))
On second thoughts - it seems as if the Xref-Join-Ind-Courses (who
comes up with such names?) is already made for this purpose. Just get
rid of the superfluous XREF_Ind_Course_ID and create the one missing
foreign key constraint.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label relationship. Show all posts
Showing posts with label relationship. Show all posts
Saturday, February 25, 2012
Possible help with query...
Ok, I have a parent table that has a child table, it is a 1-Many relationship. I want to select the single newest child from the child table with the parent table information for each parent value in the table (so if there is a parent record, i only want the newest child record). I have it working, but I am not satisfied with how I have written it. I wanted to see if there were any other opinions on the matter. The way I have it written there *could* be duplicates if somehow the date/time stamps are the same on the created column
here is an example:
DECLARE @.ParentTableTABLE ( ParentIdUNIQUEIDENTIFIER , TitleNVARCHAR(20) )
DECLARE @.ChildTableTABLE ( ChildIdUNIQUEIDENTIFIER , ParentIdUNIQUEIDENTIFIER , TitleNVARCHAR(20) , CreatedDATETIME )INSERT INTO @.ParentTable ( ParentId , Title )VALUES ('00000000-0000-0000-0000-000000000001' ,'Option A' )
INSERT INTO @.ParentTable ( ParentId , Title )VALUES ('00000000-0000-0000-0000-000000000002' ,'Option B' )
INSERT INTO @.ParentTable ( ParentId , Title )VALUES ('00000000-0000-0000-0000-000000000003' ,'Option C' )INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('10000000-0000-0000-0000-000000000001' ,'00000000-0000-0000-0000-000000000001' ,'Child A - 1' ,'5/4/2007 4:00:00 PM' )
INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('10000000-0000-0000-0000-000000000002' ,'00000000-0000-0000-0000-000000000001' ,'Child A - 2' ,'5/3/2007 4:00:00 PM' )
INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('10000000-0000-0000-0000-000000000003' ,'00000000-0000-0000-0000-000000000001' ,'Child A - 3' ,'5/2/2007 4:00:00 PM' )
INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('10000000-0000-0000-0000-000000000004' ,'00000000-0000-0000-0000-000000000001' ,'Child A - 4' ,'5/1/2007 4:00:00 PM' )INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('20000000-0000-0000-0000-000000000001' ,'00000000-0000-0000-0000-000000000002' ,'Child B - 1' ,'5/1/2007 4:00:00 PM' )
INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('20000000-0000-0000-0000-000000000002' ,'00000000-0000-0000-0000-000000000002' ,'Child B - 2' ,'5/10/2007 4:00:00 PM' )
INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('20000000-0000-0000-0000-000000000003' ,'00000000-0000-0000-0000-000000000002' ,'Child B - 3' ,'5/2/2007 4:00:00 PM' )INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('30000000-0000-0000-0000-000000000001' ,'00000000-0000-0000-0000-000000000003' ,'Child C - 1' ,'5/1/2007 4:00:00 PM' )
INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('30000000-0000-0000-0000-000000000002' ,'00000000-0000-0000-0000-000000000003' ,'Child C - 2' ,'5/2/2007 4:00:00 PM' )
INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('30000000-0000-0000-0000-000000000003' ,'00000000-0000-0000-0000-000000000003' ,'Child C - 3' ,'5/3/2007 4:00:00 PM' )
INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('30000000-0000-0000-0000-000000000004' ,'00000000-0000-0000-0000-000000000003' ,'Child C - 4' ,'5/4/2007 4:00:00 PM' )
INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('30000000-0000-0000-0000-000000000005' ,'00000000-0000-0000-0000-000000000003' ,'Child C - 5' ,'5/5/2007 4:00:00 PM' )
INSERT INTO @.ChildTable ( ChildId , ParentId , Title , Created )VALUES ('30000000-0000-0000-0000-000000000006' ,'00000000-0000-0000-0000-000000000003' ,'Child C - 6' ,'5/6/2007 4:00:00 PM' )/**
I want to select the information from the child table and the parent table,
selecting ONLY the oldest record. There should only be 3 results.
**/
SELECT
c.ParentId ,
p.TitleAS ParentTitle ,
c.ChildId ,
c.TitleAS ChildTitle ,
c.Created
FROM
@.ChildTable c
INNERJOIN @.ParentTable pON c.ParentId = p.ParentId
INNERJOIN
(
SELECT
subc.ParentId ,
MAX(subc.Created)AS Created
FROM
@.ChildTable subc
INNERJOIN @.ParentTable subptON subc.ParentId = subpt.ParentId
GROUP BY
subc.ParentId
)AS SubPTON p.ParentId = SubPT.ParentIdAND SubPT.Created = c.Created
here are the expected results:
ParentId ParentTitle ChildId ChildTitle Created
----------- ------- ----------- ------- --------
00000000-0000-0000-0000-000000000001 Option A 10000000-0000-0000-0000-000000000001 Child A - 1 2007-05-04 16:00:00.000
00000000-0000-0000-0000-000000000002 Option B 20000000-0000-0000-0000-000000000002 Child B - 2 2007-05-10 16:00:00.000
00000000-0000-0000-0000-000000000003 Option C 30000000-0000-0000-0000-000000000006 Child C - 6 2007-05-06 16:00:00.000
Anyone have any ideas?
double post ;-(
|||SELECT *FROM (SELECT c.ParentId,p.TitleAS ParentTitle,c.ChildId,c.TitleAS ChildTitle,c.Created,row_number()over(PARTITIONBY c.ParentIDORDER BY CreatedDESC,ChildIDDESC)AS tFROM @.ChildTable cINNERJOIN @.ParentTable pON c.ParentId = p.ParentId) t1WHERE t=1|||
So, I went to try and do this as a learning experiment... I am no SQL Guru or anything, but I thought I was ok. This excersixe showed me I have lots to learn :-)
This is what I came up with, it is probably worse than yours. It is actually more code, and seems a lot worse. But I think I had some creative ideas at least :-)
The following works using 2 extra temp tables ( This is what you are doing in your sub select inner joins, I tried to find a cleaner way, in the end I think your way beat mine)
-- The workItems table actually has the three rows we want, slected from the ordered child table. But I did not know how to join with the groups, and sub queris. So again... I think yours beat mine by a lot, but I am curious what other sorts of results people might come up with.
DECLARE @.orderdChildTableTABLE ( ChildIdUNIQUEIDENTIFIER , ParentIdUNIQUEIDENTIFIER , TitleNVARCHAR(20) , CreatedDATETIME )INSERT INTO @.orderdChildTableSELECT c.ChildId, c.ParentID, c.Title, c.CreatedFROM @.ChildTable cORDER BY c.CreatedDESC|||
DECLARE @.workItemsTABLE (ParentIdUNIQUEIDENTIFIER, CreatedDATETIME)INSERT INTO @.workItemsSelect oct.ParentID,max(oct.Created)From @.orderdChildTable octGROUP BY oct.ParentIDSelect oct.ParentId, p.titleas ParentTitle, oct.ChildId, oct.TitleAS ChildTitle, oct.CreatedFrom @.workItems wiINNERJOIN @.orderdChildTable octON (wi.ParentID = oct.ParentID)AND (wi.Created = oct.Created)LEFTJOIN @.ParentTable pon p.ParentID = wi.ParentIDORDER BY ParentIDASC
Motley, your query certainly looks cleaner than either of ours. (But you do return an extra recordset... which is not really important nor does it bother me).
Can you perhaps elaborate a little, breaking down the query, as to what pieces is doing what. This looks like sql2005 specific stuff, that I am not familiar with. and perhaps others could use a small break down, which helps guide me as to what to read/study up on in my books and google.
I think the piece that I am confused/not familar with is how do I read this statment to understand what exactly it is doing:
row_number
()over(PARTITIONBY c.ParentIDORDERBY CreatedDESC,ChildIDDESC)AS tI am also a little thrown by the double From...
SELECT
*FROM( .... ()AS tFROM @.ChildTable c
INNERJOIN @.ParentTable pON c.ParentId= p.ParentId
) t1
Where t = 1
Im not sure what t1 is either, and what it is supposidly emitting.
|||ah there it is. awesome. i ran into this problem before 2005 and didn't know how to get around it. thanks. i have used the row_number function before, i don't know why i didn't think of it here|||
Motley:
SELECT *
FROM (
SELECT c.ParentId,p.TitleAS ParentTitle,c.ChildId,c.TitleAS ChildTitle,c.Created,row_number()over(PARTITIONBY c.ParentIDORDER BY CreatedDESC,ChildIDDESC)AS t
FROM @.ChildTable c
INNERJOIN @.ParentTable pON c.ParentId = p.ParentId
) t1
WHERE t=1
The query doesn't actually return another recordset, it's a subquery.
Start with:
SELECT c.ParentId,p.TitleAS ParentTitle,c.ChildId,c.TitleAS ChildTitle,c.Created,row_number()over(PARTITIONBY c.ParentIDORDER BY CreatedDESC,ChildIDDESC)AS tFROM @.ChildTable cINNERJOIN @.ParentTable pON c.ParentId = p.ParentIdthe row_number portion of that is telling sqlserver to create a column in the result named "t", and for each row within a "c.ParentID" that is ordered by Created and order by ChildID within that it should represent it's place/rank (It's similiar to rank, but not quite). So the highest Created value for each ParentID will have a value of 1, the second highest is 2, etc etc but when there is a tie with two records of the same ParentID, and Created then it will consider the one with the highest ChildID to be first, basically breaking the tie.
The outer query... SELECT * FROM ( {inner query here} ) t1 WHERE t=1 just says to only take the records where t=1.
t1 is what I am calling the subquery. You always have to give subqueries that are part of the FROM clause an alias.
|||
Motley, your query certainly looks cleaner than either of ours. (But you do return an extra recordset... which is not really important nor does it bother me).
Can you perhaps elaborate a little, breaking down the query, as to what pieces is doing what. This looks like sql2005 specific stuff, that I am not familiar with. and perhaps others could use a small break down, which helps guide me as to what to read/study up on in my books and google.
I think the piece that I am confused/not familar with is how do I read this statment to understand what exactly it is doing:
row_number()over(PARTITIONBY c.ParentIDORDERBY CreatedDESC,ChildIDDESC)AS t
I am also a little thrown by the double From...
SELECT*
FROM( .... ()AS tFROM @.ChildTable c
INNERJOIN @.ParentTable pON c.ParentId= p.ParentId
) t1
Where t = 1Im not sure what t1 is either, and what it is supposidly emitting.
The two froms come from the fact you are selecting from the inner result set.
the partition is basically a "grouping" in the rownumber function, it tells the result set to number the results based on the parentid field, thus autonumbering the children 1-X based on the parentid.
t1 refers to the "temporary result set" from the select statement in the ( ), you could also have done "AS t1" to be more direct about what you are doing
and the where t=1 tells the query you only want the results where your temporary identity is 1, thus the newest child row for each parent row
if any of that makes sense
Subscribe to:
Posts (Atom)