Need help with my query...... - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Need help with my query......

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2009-01-28 : 13:22:36

hello
Following is my query which is giving me error The column 'DocumentID' was specified multiple times for 'c'
I am doing inner join twice because I want hyperlink to be as separate column.The l.hyperlink in 1st select statement doies not contain the value I want because it is bound to different Id.

Select * from(select * from (Select s.SEtitleText,s.SystemElementID,l.SystemElementLinkageID,l.LinkageTitleText,l.LinkageDescriptionText,l.Hyperlink,l.SystemElementFrom,l.SystemElementTo,m.DocumentID From SystemElement s,Linkage l,TOCMap tp,TableOfContents tc,MasterDocument m where s.SystemElementID=l.SystemElementTo AND l.SystemElementTo=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsActiveBit=1 AND tp.IsDeletedBit=0 AND l.SystemElementFrom=1796)
a inner join

(

select MasterDocument.DocumentID
From MasterDocument
INNER JOIN UserGroupRole ON MasterDocument.DocumentID = UserGroupRole.DocumentID
INNER JOIN UserGroup ON UserGroupRole.GroupID = UserGroup.GroupID
INNER JOIN UserGroupMap ON UserGroup.GroupID = UserGroupMap.UserGroupID
INNER JOIN [User] ON UserGroupMap.UserID = [User].UserID
where UserName='hiral'
)b on a.documentid = b.documentid)
c inner join
(select hyperlink,SystemElementFrom from Linkage where SystemElementFrom=1796)d on c.SystemElementFrom=d.SystemElementFrom

please help
Thank You

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 13:28:24

[code]
SELECT *
FROM (SELECT *
FROM (SELECT S.SETITLETEXT,
S.SYSTEMELEMENTID,
L.SYSTEMELEMENTLINKAGEID,
L.LINKAGETITLETEXT,
L.LINKAGEDESCRIPTIONTEXT,
L.HYPERLINK,
L.SYSTEMELEMENTFROM,
L.SYSTEMELEMENTTO,
M.DOCUMENTID
FROM SYSTEMELEMENT S,
LINKAGE L,
TOCMAP TP,
TABLEOFCONTENTS TC,
MASTERDOCUMENT M
WHERE S.SYSTEMELEMENTID = L.SYSTEMELEMENTTO
AND L.SYSTEMELEMENTTO = TP.SYSTEMELEMENTID
AND TP.TABLEOFCONTENTSID = TC.TABLEOFCONTENTSID
AND TC.DOCUMENTID = M.DOCUMENTID
AND SEISACTIVEBIT = 1
AND TP.ISDELETEDBIT = 0
AND L.SYSTEMELEMENTFROM = 1796) A
INNER JOIN
(
SELECT MASTERDOCUMENT.DOCUMENTID
FROM MASTERDOCUMENT
INNER JOIN USERGROUPROLE
ON MASTERDOCUMENT.DOCUMENTID = USERGROUPROLE.DOCUMENTID
INNER JOIN USERGROUP
ON USERGROUPROLE.GROUPID = USERGROUP.GROUPID
INNER JOIN USERGROUPMAP
ON USERGROUP.GROUPID = USERGROUPMAP.USERGROUPID
INNER JOIN [USER]
ON USERGROUPMAP.USERID = [USER].USERID
WHERE USERNAME = 'hiral') B
ON A.DOCUMENTID = B.DOCUMENTID
INNER JOIN (SELECT HYPERLINK,
SYSTEMELEMENTFROM
FROM LINKAGE
WHERE SYSTEMELEMENTFROM = 1796)C
ON A.SYSTEMELEMENTFROM = C.SYSTEMELEMENTFROM )M)D[/code]

bluestar
Posting Yak Master

133 Posts

Posted - 2009-01-28 : 13:35:02

Thanks I modified it,but now its saying "Incorrect syntax near 'SystemElementFrom'."
please help
Thank again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-28 : 13:49:29

Well post what you modified so that we can see what you did.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

bluestar
Posting Yak Master

133 Posts

Posted - 2009-01-28 : 16:15:45

okay i edited my query a bit from previous one,and still getting the same error incorrect syntex near documentid
Select * From(select * from (Select s.SEtitleText,s.SystemElementID,l.SystemElementLinkageID,l.LinkageTitleText,l.LinkageDescriptionText,l.Hyperlink,l.SystemElementFrom,l.SystemElementTo,m.DocumentID From SystemElement s,Linkage l,TOCMap tp,TableOfContents tc,MasterDocument m where s.SystemElementID=l.SystemElementTo AND l.SystemElementTo=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsActiveBit=1 AND tp.IsDeletedBit=0 AND l.SystemElementFrom=1796 )
a inner join

(

select MasterDocument.DocumentID
From MasterDocument
INNER JOIN UserGroupRole ON MasterDocument.DocumentID = UserGroupRole.DocumentID
INNER JOIN UserGroup ON UserGroupRole.GroupID = UserGroup.GroupID
INNER JOIN UserGroupMap ON UserGroup.GroupID = UserGroupMap.UserGroupID
INNER JOIN [User] ON UserGroupMap.UserID = [User].UserID
where UserName='hiral'
)b on a.documentid = b.documentid inner join
(Select s.SystemElementID,l.LinkageTitleText,l.LinkageDescriptionText,l.Hyperlink,l.SystemElementFrom,m.DocumentID From SystemElement s,Linkage l,TOCMap tp,TableOfContents tc,MasterDocument m where s.SystemElementID=l.SystemElementFrom AND l.SystemElementFrom=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsActiveBit=1 AND tp.IsDeletedBit=0 AND l.SystemElementFrom=1796 ) c on b.documentid=c.documentid

Please help
Thank You

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-01-28 : 18:23:22

[code]
Select * From
(select * from
(Select s.SEtitleText,
s.SystemElementID,
l.SystemElementLinkageID,
l.LinkageTitleText,
l.LinkageDescriptionText,
l.Hyperlink,
l.SystemElementFrom,
l.SystemElementTo,
m.DocumentID
From SystemElement s,
Linkage l,
TOCMap tp,
TableOfContents tc,
MasterDocument m
where
s.SystemElementID=l.SystemElementTo
AND l.SystemElementTo=tp.SystemElementID
AND tp.TableOfContentsID=tc.TableOfContentsID
AND tc.DocumentID=m.DocumentID
AND SEIsActiveBit=1
AND tp.IsDeletedBit=0
AND l.SystemElementFrom=1796
) a inner join
(
select MasterDocument.DocumentID
From MasterDocument
INNER JOIN UserGroupRole ON MasterDocument.DocumentID = UserGroupRole.DocumentID
INNER JOIN UserGroup ON UserGroupRole.GroupID = UserGroup.GroupID
INNER JOIN UserGroupMap ON UserGroup.GroupID = UserGroupMap.UserGroupID
INNER JOIN [User] ON UserGroupMap.UserID = [User].UserID
where UserName='hiral'
)b on a.documentid = b.documentid
inner join
( Select s.SystemElementID,
l.LinkageTitleText,
l.LinkageDescriptionText,
l.Hyperlink,
l.SystemElementFrom,
m.DocumentID
From SystemElement s,
Linkage l,
TOCMap tp,
TableOfContents tc,
MasterDocument m
where s.SystemElementID=l.SystemElementFrom
AND l.SystemElementFrom=tp.SystemElementID
AND tp.TableOfContentsID=tc.TableOfContentsID
AND tc.DocumentID=m.DocumentID
AND SEIsActiveBit=1
AND tp.IsDeletedBit=0
AND l.SystemElementFrom=1796
) c on b.documentid=c.documentid
) d
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources