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.
| Author |
Topic |
|
btisdabomb2
Starting Member
12 Posts |
 Posted - 2006-06-16 : 13:03:32
|
| I have 3 tables. One table for links, one table for roles, and one table that joins the links and roles. Each link can be assigned multiple roles, and each role can have multiple links. I need to select all the links and the roles that go with the links, and i need to retrieve all the links even if they aren't associated with a role. Help please! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-16 : 13:44:46
|
| homework?Have a look at left outer join.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
btisdabomb2
Starting Member
12 Posts |
Posted - 2006-06-16 : 13:59:27
|
| No, itsnot homework, its job-related, and I tried using left outer join but it still doesn't come back with the the results im looking for. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-16 : 14:06:28
|
| It would help if you posted the table DDL, the query you have that is not working for you, sample data, and the output you expect.CODO ERGO SUM |
|
|
|
btisdabomb2
Starting Member
12 Posts |
Posted - 2006-06-16 : 15:26:40
|
| The tables are SWRoles(RoleId, RoleName, RoleDescription, Active) SWLinks(LinkId, LinkName, LinkURL) SWLinkRoles(RoleId, Link Id) There are also other columns but they aren't important, and the SWLinkRoles table just contains foreign keys of the other two tables. I have tried several differnent queries and they come close to getting the results I need, but not quite exactly. SELECT l.LinkName, l.LinkURL, l.Category, RoleDescription FROM SWLinks, SWRoles, SWLinkrolesLEFT JOIN SWLinks l ON l.Linkid = swlinkroles.linkid--left join swroles r on r.roleid = swlinkroles.roleid--inner join swroles r on r.roleid = swlinkroles.roleid--where swlinkroles.linkid = swlinks.linkidI have tried various forms of that query and the results are...Sales Process WEBLINK Sales Process Library NationalSales Process WEBLINK Sales Process Library RegionalSales Process WEBLINK Sales Process Library NationalSales Process WEBLINK Sales Process Library RegionalorSales Process WEBLINK Sales Process Library NationalSales Process WEBLINK Sales Process Library NationalRenewal Process WEBLINK null NationalRenewal Process WEBLINK null NationalThe results I am looking for would be...Sales Process WEBLINK Sales Process Library NationalSales Process WEBLINK Sales Process Library RegionalRenewal Process WEBLINK null null |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-16 : 15:41:39
|
| That query isn't even valid syntax.How about posting the real DDL for the tables, and the actual query that you have? Along with some samples of what the table data looks like?CODO ERGO SUM |
|
|
|
btisdabomb2
Starting Member
12 Posts |
Posted - 2006-06-16 : 15:57:35
|
| I know its not valid synax, those were just differnt joins I tried to get it to work. I can't post the actual DDL for the tables or actual sample data because of company policy. |
|
|
|
btisdabomb2
Starting Member
12 Posts |
Posted - 2006-06-16 : 16:04:27
|
| BTW... I am an intern and its Friday so not many people are in the office. Normally I would have somebody here to help but thats not possible right now. I dont know what other informtion I can actually provide for any of you to help me. I guess I'll just wait until monday morning. Thanks anyways. |
|
|
|
|
|
|
|
|
Select and join 3 tables