I am unsure of the exact syntax to use so I am reaching out for assistance on this so I don't butcher something!  Proper procedure was not explained to a new user so multiple users were set-up incorrectly.  Now I need to update all incorrect users.  For every value in #Pages.pName EVERY user MUST have a 1 or 0 value #UserPageAccess.abletoview  What has just come to light is a value has only been entered when the user has access --- so only 1's have been entered.  I need to bulk update EVERY USER to have a 0 value!

This is DDL structure and set-up for my tables.  As well as what I am wanting the database to display.

--Expected Output
pName	        User Name   abletoview
www.yahoo.com	Mike Jones	1	    
www.espn.com	Mike Jones	1	    
www.msnbc.com	Mike Jones	0	    
www.hotmail.com	Mike Jones	0	    
www.gmail.com	Mike Jones	0	    
www.yahoo.com	Bob Segar	1	    
www.espn.com	Bob Segar	0	    
www.msnbc.com	Bob Segar	0	    
www.hotmail.com	Bob Segar	0	   
www.gmail.com	Bob Segar	0	
			


--DDL & SELECT Query

Create Table #Pages
(
	pID int,
	pName varchar(100)
)

Create Table #UserPageAccess
(
	upID int,
	pID int,
	uID int,
	abletoview int
)

Create Table #Users
(
	uID int,
	fname varchar(100),
	lname varchar(100)
)

Insert Into #Pages Values (1, 'www.yahoo.com'), (2, 'www.espn.com'), (3, 'www.msnbc.com'), (4, 'www.hotmail.com'), (5, 'www.gmail.com')
INSERT Into #Users Values (1, 'Mike', 'Jones'), (2, 'Bob', 'Segar')
Insert Into #UserPageAccess Values (1, 1, 1, 1), (2, 2, 1, 1), (3, 1, 2, 1)



Select 
#Pages.pName, 
case 
when #Users.uID = 1 then 'Mike Jones'
when #Users.uID = 2 then 'Bob Segar'
end As [User Name],
case when abletoview = 1 then 1 else 0 end As [Can View],
case when abletoview = 0 then 1 else 0 end As [Unable To View]
FROM #Pages
Inner Join #UserPageAccess
ON #Pages.pID = #UserPageAccess.pID
INNER JOIN #Users
ON #Users.uID = #UserPageAccess.uID

--Drop Table #Pages
--Drop Table #UserPageAccess
--DROP TABLE #Users

Create Table #Pages
(
	pID int,
	pName varchar(100)
)

Create Table #UserPageAccess
(
	upID int,
	pID int,
	uID int,
	abletoview int
)

Create Table #Users
(
	uID int,
	fname varchar(100),
	lname varchar(100)
)

Insert Into #Pages Values (1, 'www.yahoo.com'), (2, 'www.espn.com'), (3, 'www.msnbc.com'), (4, 'www.hotmail.com'), (5, 'www.gmail.com')
INSERT Into #Users Values (1, 'Mike', 'Jones'), (2, 'Bob', 'Segar')
Insert Into #UserPageAccess Values (1, 1, 1, 1), (2, 2, 1, 1), (3, 1, 2, 1)


Select A.PName,A.fname +' '+A.Lname as [Name], isnull(B.AbletoView,0) as AbleToView
from
(Select 
#Pages.pName, #users.fname,#users.Lname,#Pages.pID,#Users.Uid
--,case when abletoview = 1 then 1 else 0 end As [Can View],
--case when abletoview = 0 then 1 else 0 end As [Unable To View]
FROM #Pages, #Users) A LEFT OUTER JOIN #UserPageAccess B on A.PId=B.Pid and B.Uid=A.Uid



Drop Table #Pages
Drop Table #UserPageAccess
DROP TABLE #Users

No update needed!  Thank you so much @Stan210

EDIT ---

How could I break out out into 2 columns like I was doing in my case statements above?

• Edited by Baby_Christopher 11 hours 39 minutes ago

How could I break out out into 2 columns like I was doing in my case statements above?

How could I break out out into 2 columns like I was doing in my case statements above?

Create Table #Pages
(
	pID int,
	pName varchar(100)
)

Create Table #UserPageAccess
(
	upID int,
	pID int,
	uID int,
	abletoview int
)

Create Table #Users
(
	uID int,
	fname varchar(100),
	lname varchar(100)
)

Insert Into #Pages Values (1, 'www.yahoo.com'), (2, 'www.espn.com'), (3, 'www.msnbc.com'), (4, 'www.hotmail.com'), (5, 'www.gmail.com')
INSERT Into #Users Values (1, 'Mike', 'Jones'), (2, 'Bob', 'Segar')
Insert Into #UserPageAccess Values (1, 1, 1, 1), (2, 2, 1, 1), (3, 1, 2, 1)


Select A.PName,A.fname +' '+A.Lname as [Name], 
case when abletoview = 1 then 1 else 0 end As [Can View],
case when abletoview is null then 1 else 0 end As [Unable To View]
from
(Select 
#Pages.pName, #users.fname,#users.Lname,#Pages.pID,#Users.Uid
FROM #Pages, #Users) A LEFT OUTER JOIN #UserPageAccess B on A.PId=B.Pid and B.Uid=A.Uid



Drop Table #Pages
Drop Table #UserPageAccess
DROP TABLE #Users

Gee, haven't we seen this awful scheama beofre? Is it part of a bad class assignmetn? Why did you not learn anythign from that last posting? 

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI-ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constriants on the table. Avoid dialect in favor of ANSI-ISO Standard SQL. 

And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells-

Your DDL is still wrong; no keys, no constraints, no DRI, and incorrect data types allong with ISO-11179 violatiosn. Just like the last posting! Here is another repair job, but it is still a mess

CREATE TABLE Pages
(page_id INTEGER NOT NULL PRIMARY KEY,
 page_name VARCHAR(256) NOT NULL);

INSERT INTO Pages 
VALUES 
(1, 'www.yahoo.com'),
(2, 'www.espn.com'), 
(3, 'www.msnbc.com'), 
(4, 'www.hotmail.com'), 
(5, 'www.gmail.com');

CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY,
 user_firstname VARCHAR(20) NOT NULL,
 user_lastname VARCHAR(20) NOT NULL);

INSERT INTO Users 
VALUES 
(1, 'Mike', 'Jones'),
(2, 'Bob', 'Segar');

The relationship table is a total disaster. Why do you have that absurd UP_id? Let me answer my own quesiton. You magnetic tape file proigrmmres neeed to have a phsyuical reocrde number in every file; this is nothing like a key in RDBMS. 
Where are thte DRI actions? Anser: they do  not exist in mag tape files, so you do not use tehm in SQL.

CREATE TABLE User_Page_Access
(page_id INTEGER NOT NULL
 REFERENCES Pages(page_id)
 ON DELETE CASCADE,
 user_id INTEGER NOT NULL
 REFERENCES Users(user_id)
 PRIMARY KEY (page_id, user_id),
 access_flg DEFAULT 0 INTEGER NOT NULL
 CHECK (access_flg IN (0,1))
);


>> What has just come to light is a value has only been entered when the user has access --- so only 1's have been entered. I need to bulk update EVERY USER to have a 0 value!  <<

INSERT INTO User_Page_Access
SELECT P.page_id, U.user_id, 0
   FROM Pages AS P
        CROSS JOIN 
        Users AS U;

Now make updates:

CREATE PROCEDURE Grant_Access
(@in_user_id INTEGER,
 @in_page_id INTEGER)
UPDATE User_Page_Access
   SET access_flg = 1
 WHERE user_id = @in_user_id,
   AND page_id = @in_page_id;

No update needed!  Thank you so much @Stan210

EDIT ---

How could I break out out into 2 columns like I was doing in my case statements above?

• Edited by Baby_Christopher Friday, May 22, 2015 7:27 PM