Results with M:N relationship- duplicate ID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Results with M:N relationship- duplicate ID

Author

Topic

emdigangi
Starting Member

4 Posts

Posted - 2013-02-22 : 09:23:00

If you have an M:N relationship with a bridge table, what can you do when say you get two or more results that have the same unique identifer but vary just by one of the fields? Can you combine those two fields somehow or perhaps just choose one to display?

To illustrate:

A given document has many subjects. A subject may be in many documents. We have tables Document and Subject with bridge table Assign.

An example of results:

Doc_ID Doc_Title Doc_Author Subject_Nam
1 Big Bad Wolf DiGangi, Caroline Animals
1 Big Bad Wolf DiGangi, Caroline Family
2 Little House Lambert, Regina History

Is there a function that would allow the combination of the Subject_Name field in results or perhaps just display one of the subjects? Any other thoughts how you would approach this?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-22 : 09:30:03

If it doesn't matter which record you display, you can always just take a MAX
SELECT Doc_ID,Doc_Title ,Doc_Author ,MAX(Subject_Nam) as Subject_Nam
FROM <your tables>
GROUP BY Doc_ID,Doc_Title ,Doc_Author

Jim

Everyday I learn something that somebody else already knew

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-02-22 : 09:37:17

There are a few options..you could either use "FOR XML PATH" to get a delimited list of all the subjects in one column or you could just get ie get the first one:


WITH cte AS (
  select *, rownum = row_number() over (partition by Doc_ID ORDER BY Subject_Nam)
  from table
  )
select * from cte where rownum = 1

- Lumbago
My blog-> http://thefirstsql.com

emdigangi
Starting Member

4 Posts

Posted - 2013-02-22 : 10:26:38

I used your code and it worked! :) Can you tell me about the "FOR XML PATH"?

Thanks,
Easter

quote:
Originally posted by Lumbago

There are a few options..you could either use "FOR XML PATH" to get a delimited list of all the subjects in one column or you could just get ie get the first one:

WITH cte AS (
select *, rownum = row_number() over (partition by Doc_ID ORDER BY Subject_Nam)
from table
)
select * from cte where rownum = 1

- Lumbago
My blog-> http://thefirstsql.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-22 : 12:13:08

Maybe this will help?

http://msdn.microsoft.com/en-us/library/ms178107.aspx

emdigangi
Starting Member

4 Posts

Posted - 2013-02-22 : 13:44:54

How do I utilize this if I add another bridge to the query? Below is what I tried.

WITH cte AS (
select D.DOC_ID, I.INDIV_LNAME, I.INDIV_FNAME, S.SUBJ_CLASSN, rownum = row_number() over (partition by Doc_ID ORDER BY S.SUBJ_CLASSN, I.INDIV_LNAME, I.INDIV_FNAME)
from (((DOCUMENT D INNER JOIN ASSIGN A ON D.DOC_ID=A.DOC_IDfkA)
INNER JOIN SUBJECT S ON S.SUBJ_ID=A.SUBJ_IDfkA
INNER JOIN INVOLVE V ON V.DOC_IDfkI=D.DOC_ID)
INNER JOIN INDIVIDUAL I
ON V.INDIV_IDfkI=I.INDIV_ID
)
select * from cte where rownum = 1
ORDER BY INDIV_LNAME ASC;

I get an error saying "Incorrect syntax near the keyword 'select'."

Thanks in advance for anyone's help.

quote:
Originally posted by Lumbago

There are a few options..you could either use "FOR XML PATH" to get a delimited list of all the subjects in one column or you could just get ie get the first one:

WITH cte AS (
select *, rownum = row_number() over (partition by Doc_ID ORDER BY Subject_Nam)
from table
)
select * from cte where rownum = 1

- Lumbago
My blog-> http://thefirstsql.com

emdigangi
Starting Member

4 Posts

Posted - 2013-02-22 : 14:04:20

Thanks! It is a little intimidating. Not sure how to start. Any thoughts?

quote:
Originally posted by Lamprey

Maybe this will help?

http://msdn.microsoft.com/en-us/library/ms178107.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources