Little help with a 3 table query...

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

SQL Server 2000 Forums

Transact-SQL (2000)

Little help with a 3 table query...

Author

Topic

surreal
Starting Member

4 Posts

Posted - 2007-07-03 : 08:10:34

Hi,

I have 3 tables in my database:

ADMIN
login_id (int)(PK)
user_name (varchar)

PAGES
page_id (int)(PK)
page_name (varchar)
page_level (int)

PERMISSIONS
login_id (int)(FK)
page_id (int)(FK)
status (bit)

basically the PERMISSIONS table sets access rights to users for pages. What I'm trying to do is pull out all the pages and the status for a particular login_id. I need to list ALL the pages whether or not there is an entry for them in the PERMISSIONS table, so that a permission for that page can be set (I'm using the data to build the data entry grid in ASP.Net) . Problem I'm having is that if I specify a login_id for a user that has no entries in the PERMISSIONS table, it doesn't list the pages that DO have an entry, regardless of their login_id... hope that makes sense, its kind of difficult to put into words ;-)

I've been poking around in the Query Designer of SQL Server 2000 trying to get it to do what I need but I can't find a solution. I figure its quite a common thing to want to do so hopefully someone can steer me in the right direction, or suggest a different way to achieve the same thing.

Any help / advice appreciated.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 08:40:33

You need to use LEFT JOIN between PAGES and PERMISSIONS table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

surreal
Starting Member

4 Posts

Posted - 2007-07-03 : 09:31:28

Thanks for the reply but that on its own doesn't do what I'm trying to do. I've tried the following TSQL:

SELECT permissions.status, pages.page_name
FROM permissions
RIGHT OUTER JOIN pages
ON
permissions.page_id = pages.page_id
WHERE
(permissions.login_id = 4) OR (permissions.login_id IS NULL)

which produces a full list of pages. Problem is if I'm looking for login_id=4 in the PERMISSIONS table, and there are no entries for that but there ARE entries for another login_id (lets say 2 for example) the pages listed will exclude the pages that have entries in the PERMISSIONS table.

I think I might have to find another way around this rather than trying to pull the data with one query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 09:55:03

SELECT permissions.status, pages.page_name
FROM permissions
RIGHT OUTER JOIN pages
ON
permissions.page_id = pages.page_id and permissions.login_id = 4

--or

SELECT pages.page_name, permissions.status
FROM pages
left JOIN permissions ON permissions.page_id = pages.page_id and permissions.login_id = 4

Peter Larsson
Helsingborg, Sweden

surreal
Starting Member

4 Posts

Posted - 2007-07-03 : 10:10:29

quote:
Originally posted by Peso

SELECT permissions.status, pages.page_name
FROM permissions
RIGHT OUTER JOIN pages
ON
permissions.page_id = pages.page_id and permissions.login_id = 4

--or

SELECT pages.page_name, permissions.status
FROM pages
left JOIN permissions ON permissions.page_id = pages.page_id and permissions.login_id = 4

Peter Larsson
Helsingborg, Sweden

That's what I was trying to do!!! Thanks so much for your help... much appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources