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 |
|
tunafish24
Starting Member
3 Posts |
 Posted - 2009-07-16 : 18:43:17
|
| The query I came up with:SELECT * FROM Computers LEFT OUTER JOIN AccessControlList ON Computers.ComputerID = AccessControlList.ComputerIDWHERE Computers.OwnerUsernameID = 2It works, but does Not give the required result. The Computers table has a list of all the Computers and AccessControlList has entries for only those Users that are allowed to Access Computers. So for many computers there is No entry in AccessControlList because user(s) aren't given permission for that computer.That is why OUTER JOIN statement isn't working as I want i.e. that it should return all Computers even if there isn't a matching entry in AccessControlList. It should even return all computers if the AccessControlList table is Empty. But it's not working that way, any suggestions/ideas? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-17 : 14:11:58
|
| can you explain with some sample data and output why you thinking its not working right way?your query seems to do like what you actually asked for |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-17 : 15:10:48
|
WHERE Computers.OwnerUsernameID = 2If you are using a where condition then maybe you don't want your select to return ALL rows?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
tunafish24
Starting Member
3 Posts |
Posted - 2009-07-17 : 22:08:01
|
| I'm really confused here guys, I think I might be using the wrong type of JOIN. So here's my problem, I'd appreciate if anyone can help me write a sql query for this.I have two tables, that I need to pull data out of, for this query. The first one is Computers table, this table has a list of all the computers. The second one is Access Control List (ACL) table, this table will contain any entry with respective user's ID & Computer ID, ONLY IF the user is given permission by the administrator. Otherwise there won't be any entry in the Access Control List for that User/Computer. For any user, I have to display a list of all computers and show which computers the user is allowed to access and which he/she isn't. I was planning on using the entry in ACL as a way of confirming access and if there isn't any entry then show it as Access not allowed. So I decided to use LEFT OUTER JOIN, that way I'll definitely get a list of all the Computers and by joining it with ACL it will just show NULLs for missing entries in ACL table. The problem is that it doesn't do that, It only shows Computer entries that have corresponding entries in ACL table. So what wrong with my SQL code, any suggestions? |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2009-07-18 : 19:41:22
|
You don't want the WHERE Computer.OwnerUsernameID = 2 because that cuts down the list of computers. What is the column in the AccessControlList that equates to a user?--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! ""RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row" |
|
|
|
tunafish24
Starting Member
3 Posts |
Posted - 2009-07-18 : 23:35:39
|
| Actually, I need to use WHERE clause to limit the computers to only those that the current administrator is allowed to see. The main problem is that if there isn't any corresponding row in ACL table the query doesn't show the computer entry at all.the ACL table has a UsernameID column that equates to a specific user. |
|
|
|
|
|
|
|
|
Join two tables, One with missing rows