Help with query using inner join

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

Help with query using inner join

Author

Topic

ostinoh
Yak Posting Veteran

66 Posts

Posted - 2010-03-25 : 16:10:32

Hi - I'm using the inner join command to pull data from multiple tables. The issue I'm having is my column 'login' has sometimes 2-5 entries for the same name for the same URL listed. How can I query that only one name will show up for that persons login? I included my script . I blocked out the one part for security reasons.

Any help would be greatly appreciated.

select

'http://clientzone.brulant.com/' + w.FullURL as [URL],
w.Title,
ui.tp_login,
wg.Title,
ud.tp_Created

FROM

UserInfo ui

inner join WebGroups wg on wg.SiteId = ui.tp_SiteId

inner join WebGroupmembership wgm on wgm.WebId = wg.WebId and wgm.GroupID = wg.[ID] and ui.tp_ID = wgm.memberID

inner join Sites s on s.[Id] = ui.tp_SiteID

inner join Webs w on w.SiteId = ui.tp_SiteID

inner join UserData ud on ud.tp_SiteId = ui.tp_SiteId and ud.tp_id = ui.tp_ID

where
--Remove the Portal Areas and Personal Sites
ud.tp_Created BETWEEN '2009-02-01' AND '2009-02-28'
and
w.FullURL not like '%personal%'
and
w.Title not in
('name of area you dont want', 'Administration', 'another area you dont want to list')
and
w.FullUrl <> 'MySite'

order by
[URL]

Thank you..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-25 : 16:40:39

Are you using SQL Server 2005/2008?

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

Subscribe to my blog

ostinoh
Yak Posting Veteran

66 Posts

Posted - 2010-03-25 : 17:17:33

Using SQL Server 2005

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-25 : 17:31:38

Then you can make use of the ROW_NUMBER() function. Do a quick search in these forums for examples. I'd post a solution for you, but I don't have SQL Server in front of me at the moment.

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

Subscribe to my blog

ostinoh
Yak Posting Veteran

66 Posts

Posted - 2010-03-25 : 21:13:31

Hi - I'm having a time seeing where I would put this into my query and info I need to fill in. I took a look at everyone examples but still could not get it to work. Can you shed some light on this?

Thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources