| Author |
 Topic  |
|
|
emdigangi
Starting Member
4 Posts |
 Posted - 02/22/2013 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 02/22/2013 : 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
Norway
3241 Posts |
Posted - 02/22/2013 : 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 - 02/22/2013 : 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
Flowing Fount of Yak Knowledge
3833 Posts |
|
|
emdigangi
Starting Member
4 Posts |
Posted - 02/22/2013 : 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 |
|
| |
Topic |
|
Results with M:N relationship- duplicate ID
