SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Results with M:N relationship- duplicate ID
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

emdigangi
Starting Member

4 Posts

Posted - 02/22/2013 :  09:23:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/22/2013 :  09:30:03  Show Profile  Reply with Quote
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
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 02/22/2013 :  09:37:17  Show Profile  Reply with Quote
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
Go to Top of Page

emdigangi
Starting Member

4 Posts

Posted - 02/22/2013 :  10:26:38  Show Profile  Reply with Quote
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

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4360 Posts

Posted - 02/22/2013 :  12:13:08  Show Profile  Reply with Quote
Maybe this will help?

http://msdn.microsoft.com/en-us/library/ms178107.aspx
Go to Top of Page

emdigangi
Starting Member

4 Posts

Posted - 02/22/2013 :  13:44:54  Show Profile  Reply with Quote
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

Go to Top of Page

emdigangi
Starting Member

4 Posts

Posted - 02/22/2013 :  14:04:20  Show Profile  Reply with Quote
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


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000