Why Is My Query Returning Nulls

I only want to return the criteria of my where clause which is done, but their is also an additional row that shows null for all possible values?

This is the dataset I am returned, and the dataset I want returned

--What is returned
pName	Permission Granted	Permission Denied	Salesmanfirstname	Salesmanlastname
Janitor Closet	1	0	NULL	NULL
Janitor Closet	1	0	Miles	Davis
Exterior Storage Room	1	0	NULL	NULL
Exterior Storage Room	0	0	Miles	Davis
Alarm Control Room	0	0	NULL	NULL
Alarm Control Room	1	0	Miles	Davis
Sprinkler Backflow Room	1	0	NULL	NULL
Sprinkler Backflow Room	0	0	Miles	Davis
Exterior Left Emergency Door	0	0	NULL	NULL
Main Entrance	1	0	NULL	NULL
				
				
--What I want returned				
pName	Permission Granted	Permission Denied	Salesmanfirstname	Salesmanlastname
Janitor Closet	1	0	Miles	Davis
Exterior Storage Room	0	0	Miles	Davis
Alarm Control Room	1	0	Miles	Davis
Sprinkler Backflow Room	0	0	Miles	Davis

And this is my DDL

Create Table #1
(
    [pID] int,
    [pName] varchar(100)
)

Create Table #2
(
	[upID] int,
	[pID] int,
	[uID] int,
	[accessgranted] int
)

Create Table #3
(
	[uID] int,
	[Salesmanfirstname] varchar(100),
	[Salesmanlastname] varchar(100)
)
go

INSERT INTO #1 VALUES (1, 'Janitor Closet'), (2, 'Exterior Storage Room'), (3, 'Alarm Control Room'), (4, 'Sprinkler Backflow Room'), (5, 'Exterior Left Emergency Door'), (6, 'Main Entrance')
INSERT INTO #2 VALUES (1, 1, 1, 1), (2, 2, 1, 1), (3, 3, 1, 0), (4, 4, 1, 1), (5, 5, 1, 0), (6, 6, 1, 1), (7,1,2,1), (8,2,2,0), (9,3,2,1), (10,4,2,0)
INSERT INTO #3 VALUES (1, 'Big', 'Train'), (2, 'Miles', 'Davis')


SELECT #1.pName
,case when #2.accessgranted = 1 then 1 else 0 end As [Access Granted]
,case when #2.accessgranted = 0 then 0 else 0 end As [Access Denied]
,#3.Salesmanfirstname
,#3.Salesmanlastname
FROM #1
LEFT OUTER JOIN #2
ON #2.pID = #1.pID
LEFT OUTER JOIN #3
ON #3.uID = #2.uID and #3.Salesmanlastname = 'Davis'


--DROP TABLE #1
--DROP TABLE #2
--DROP TABLE #3

May 21st, 2015 2:18pm

Change to an inner join on #3 and your problem goes away.
Or, use the join criteria you have for join to table#3 as ON #3.uID = #2.uI .... and move the salesmanlastname='Davis' to the where clause.

Free Windows Admin Tool Kit Click here and download it now
May 21st, 2015 2:21pm

Change to an inner join on #3 and your problem goes away.
Or, use the join criteria you have for join to table#3 as ON #3.uID = #2.uI .... and move the salesmanlastname='Davis' to the where clause.

Doing this takes away all of the options from pName?  I want to see all options avaliable for pName and what the salesman is granted is denied access to
May 21st, 2015 2:25pm
Create Table #1
(
    [pID] int,
    [pName] varchar(100)
)

Create Table #2
(
	[upID] int,
	[pID] int,
	[uID] int,
	[accessgranted] int
)

Create Table #3
(
	[uID] int,
	[Salesmanfirstname] varchar(100),
	[Salesmanlastname] varchar(100)
)
go

INSERT INTO #1 VALUES (1, 'Janitor Closet'), (2, 'Exterior Storage Room'), (3, 'Alarm Control Room'), (4, 'Sprinkler Backflow Room'),
 (5, 'Exterior Left Emergency Door'), (6, 'Main Entrance')
INSERT INTO #2 VALUES (1, 1, 1, 1), (2, 2, 1, 1), (3, 3, 1, 0), (4, 4, 1, 1), (5, 5, 1, 0), (6, 6, 1, 1), (7,1,2,1), (8,2,2,0), (9,3,2,1), (10,4,2,0)
INSERT INTO #3 VALUES (1, 'Big', 'Train'), (2, 'Miles', 'Davis')


SELECT #1.pName
,case when #2.accessgranted = 1 then 1 else 0 end As [Access Granted]
,case when #2.accessgranted = 0 then 0 else 0 end As [Access Denied]
,#3.Salesmanfirstname
,#3.Salesmanlastname
FROM #1
LEFT OUTER JOIN #2
ON #2.pID = #1.pID
INNER JOIN #3
ON #3.uID = #2.uID and #3.Salesmanlastname = 'Davis'


DROP TABLE #1
DROP TABLE #2
DROP TABLE #3

Free Windows Admin Tool Kit Click here and download it now
May 21st, 2015 2:29pm
Create Table #1
(
    [pID] int,
    [pName] varchar(100)
)

Create Table #2
(
	[upID] int,
	[pID] int,
	[uID] int,
	[accessgranted] int
)

Create Table #3
(
	[uID] int,
	[Salesmanfirstname] varchar(100),
	[Salesmanlastname] varchar(100)
)
go

INSERT INTO #1 VALUES (1, 'Janitor Closet'), (2, 'Exterior Storage Room'), (3, 'Alarm Control Room'), (4, 'Sprinkler Backflow Room'),
 (5, 'Exterior Left Emergency Door'), (6, 'Main Entrance')
INSERT INTO #2 VALUES (1, 1, 1, 1), (2, 2, 1, 1), (3, 3, 1, 0), (4, 4, 1, 1), (5, 5, 1, 0), (6, 6, 1, 1), (7,1,2,1), (8,2,2,0), (9,3,2,1), (10,4,2,0)
INSERT INTO #3 VALUES (1, 'Big', 'Train'), (2, 'Miles', 'Davis')


SELECT #1.pName
,case when #2.accessgranted = 1 then 1 else 0 end As [Access Granted]
,case when #2.accessgranted = 0 then 0 else 0 end As [Access Denied]
,#3.Salesmanfirstname
,#3.Salesmanlastname
FROM #1
LEFT OUTER JOIN #2
ON #2.pID = #1.pID
INNER JOIN #3
ON #3.uID = #2.uID and #3.Salesmanlastname = 'Davis'


DROP TABLE #1
DROP TABLE #2
DROP TABLE #3
May 21st, 2015 2:31pm

Explanation of what's happening...

Consider this code style:

Select {list} from Table a left join table b on {join criteria} where {filter criteria}

Table b is filtered by the join criteria, but the results are then outer joined to table a. Therefore, all of the table a records that are NOT directly joined to table b will be included. The SQL FROM Clause is evaluated in full, and then the WHERE clause is evaluated against the results of the FROM clause.

Since you're searching for a specific value in table b, no null value from table b will be included, and the result of what you are looking for is equivalent to an inner join.

Free Windows Admin Tool Kit Click here and download it now
May 21st, 2015 2:33pm

Create Table #1
(
    [pID] int,
    [pName] varchar(100)
)

Create Table #2
(
	[upID] int,
	[pID] int,
	[uID] int,
	[accessgranted] int
)

Create Table #3
(
	[uID] int,
	[Salesmanfirstname] varchar(100),
	[Salesmanlastname] varchar(100)
)
go

INSERT INTO #1 VALUES (1, 'Janitor Closet'), (2, 'Exterior Storage Room'), (3, 'Alarm Control Room'), (4, 'Sprinkler Backflow Room'),
 (5, 'Exterior Left Emergency Door'), (6, 'Main Entrance')
INSERT INTO #2 VALUES (1, 1, 1, 1), (2, 2, 1, 1), (3, 3, 1, 0), (4, 4, 1, 1), (5, 5, 1, 0), (6, 6, 1, 1), (7,1,2,1), (8,2,2,0), (9,3,2,1), (10,4,2,0)
INSERT INTO #3 VALUES (1, 'Big', 'Train'), (2, 'Miles', 'Davis')


SELECT #1.pName
,case when #2.accessgranted = 1 then 1 else 0 end As [Access Granted]
,case when #2.accessgranted = 0 then 0 else 0 end As [Access Denied]
,#3.Salesmanfirstname
,#3.Salesmanlastname
FROM #1
LEFT OUTER JOIN #2
ON #2.pID = #1.pID
INNER JOIN #3
ON #3.uID = #2.uID and #3.Salesmanlastname = 'Davis'


DROP TABLE #1
DROP TABLE #2
DROP TABLE #3
May 21st, 2015 2:33pm

Create Table #1
(
    [pID] int,
    [pName] varchar(100)
)

Create Table #2
(
	[upID] int,
	[pID] int,
	[uID] int,
	[accessgranted] int
)

Create Table #3
(
	[uID] int,
	[Salesmanfirstname] varchar(100),
	[Salesmanlastname] varchar(100)
)
go

INSERT INTO #1 VALUES (1, 'Janitor Closet'), (2, 'Exterior Storage Room'), (3, 'Alarm Control Room'), (4, 'Sprinkler Backflow Room'),
 (5, 'Exterior Left Emergency Door'), (6, 'Main Entrance')
INSERT INTO #2 VALUES (1, 1, 1, 1), (2, 2, 1, 1), (3, 3, 1, 0), (4, 4, 1, 1), (5, 5, 1, 0), (6, 6, 1, 1), (7,1,2,1), (8,2,2,0), (9,3,2,1), (10,4,2,0)
INSERT INTO #3 VALUES (1, 'Big', 'Train'), (2, 'Miles', 'Davis')


SELECT #1.pName
,case when #2.accessgranted = 1 then 1 else 0 end As [Access Granted]
,case when #2.accessgranted = 0 then 0 else 0 end As [Access Denied]
,#3.Salesmanfirstname
,#3.Salesmanlastname
FROM #1
LEFT OUTER JOIN #2
ON #2.pID = #1.pID
INNER JOIN #3
ON #3.uID = #2.uID and #3.Salesmanlastname = 'Davis'


DROP TABLE #1
DROP TABLE #2
DROP TABLE #3
Free Windows Admin Tool Kit Click here and download it now
May 21st, 2015 2:38pm

Explanation of what's happening...

Consider this code style:

Select {list} from Table a left join table b on {join criteria} where {filter criteria}

Table b is filtered by the join criteria, but the results are then outer joined to table a. Therefore, all of the table a records that are NOT directly joined to table b will be included. The SQL FROM Clause is evaluated in full, and then the WHERE clause is evaluated against the results of the FROM clause.

Since you're searching for a specific value in table b, no null value from table b will be included, and the result of what you are looking for is equivalent to an inner join.

I obviously did a terrible job of copy/paste in my initial question to show my desired output and I sorry for that.  This is the result set that I want to be returned. (ALL ENTRIES from #1.pName)

pName	Permission Granted	Permission Denied	Salesmanfirstname	Salesmanlastname
Janitor Closet	1	0	Miles	Davis
Exterior Storage Room	0	1	Miles	Davis
Alarm Control Room	1	0	Miles	Davis
Sprinkler Backflow Room	0	1	Miles	Davis
Exterior Left Emergency Door	0	1	Miles	Davis
Main Entrance	0	1	Miles	Davis


May 21st, 2015 2:39pm
how come Exterior Left Emergency Door and Main Entrance can have Davis Miles as names -- that is not correct.. try this 
Create Table #1
(
    [pID] int,
    [pName] varchar(100)
)

Create Table #2
(
	[upID] int,
	[pID] int,
	[uID] int,
	[accessgranted] int
)

Create Table #3
(
	[uID] int,
	[Salesmanfirstname] varchar(100),
	[Salesmanlastname] varchar(100)
)
go

INSERT INTO #1 VALUES (1, 'Janitor Closet'), (2, 'Exterior Storage Room'), (3, 'Alarm Control Room'), (4, 'Sprinkler Backflow Room'),
 (5, 'Exterior Left Emergency Door'), (6, 'Main Entrance')
INSERT INTO #2 VALUES (1, 1, 1, 1), (2, 2, 1, 1), (3, 3, 1, 0), (4, 4, 1, 1), (5, 5, 1, 0), (6, 6, 1, 1), (7,1,2,1), (8,2,2,0), (9,3,2,1), (10,4,2,0)
INSERT INTO #3 VALUES (1, 'Big', 'Train'), (2, 'Miles', 'Davis')


;with CTE AS (SELECT #1.pName
,case when #2.accessgranted = 1 then 1 else 0 end As [Access Granted]
,case when #2.accessgranted = 0 then 0 else 0 end As [Access Denied]
,#3.Salesmanfirstname
,#3.Salesmanlastname
FROM #1
LEFT OUTER JOIN #2
ON #2.pID = #1.pID
INNER JOIN #3
ON #3.uID = #2.uID and #3.Salesmanlastname = 'Davis')
,CTE2 as (SELECT #1.pName
,case when #2.accessgranted = 1 then 1 else 0 end As [Access Granted]
,case when #2.accessgranted = 0 then 0 else 0 end As [Access Denied],null as FirstName,null as [LastName]
FROM #1
LEFT OUTER JOIN #2
ON #2.pID = #1.pID where #1.PName not in (select distinct Pname from CTE))

select * from CTE
Union all
select * from CTE2


DROP TABLE #1
DROP TABLE #2
DROP TABLE #3

Free Windows Admin Tool Kit Click here and download it now
May 21st, 2015 2:41pm
how come Exterior Left Emergency Door and Main Entrance can have Davis Miles as names -- that is not correct.. try this 
Create Table #1
(
    [pID] int,
    [pName] varchar(100)
)

Create Table #2
(
	[upID] int,
	[pID] int,
	[uID] int,
	[accessgranted] int
)

Create Table #3
(
	[uID] int,
	[Salesmanfirstname] varchar(100),
	[Salesmanlastname] varchar(100)
)
go

INSERT INTO #1 VALUES (1, 'Janitor Closet'), (2, 'Exterior Storage Room'), (3, 'Alarm Control Room'), (4, 'Sprinkler Backflow Room'),
 (5, 'Exterior Left Emergency Door'), (6, 'Main Entrance')
INSERT INTO #2 VALUES (1, 1, 1, 1), (2, 2, 1, 1), (3, 3, 1, 0), (4, 4, 1, 1), (5, 5, 1, 0), (6, 6, 1, 1), (7,1,2,1), (8,2,2,0), (9,3,2,1), (10,4,2,0)
INSERT INTO #3 VALUES (1, 'Big', 'Train'), (2, 'Miles', 'Davis')


;with CTE AS (SELECT #1.pName
,case when #2.accessgranted = 1 then 1 else 0 end As [Access Granted]
,case when #2.accessgranted = 0 then 0 else 0 end As [Access Denied]
,#3.Salesmanfirstname
,#3.Salesmanlastname
FROM #1
LEFT OUTER JOIN #2
ON #2.pID = #1.pID
INNER JOIN #3
ON #3.uID = #2.uID and #3.Salesmanlastname = 'Davis')
,CTE2 as (SELECT #1.pName
,case when #2.accessgranted = 1 then 1 else 0 end As [Access Granted]
,case when #2.accessgranted = 0 then 0 else 0 end As [Access Denied],null as FirstName,null as [LastName]
FROM #1
LEFT OUTER JOIN #2
ON #2.pID = #1.pID where #1.PName not in (select distinct Pname from CTE))

select * from CTE
Union all
select * from CTE2


DROP TABLE #1
DROP TABLE #2
DROP TABLE #3
May 21st, 2015 2:44pm

how come Exterior Left Emergency Door and Main Entrance can have Davis Miles as names -- that is not correct.. try this 

Create Table #1
(
    [pID] int,
    [pName] varchar(100)
)

Create Table #2
(
	[upID] int,
	[pID] int,
	[uID] int,
	[accessgranted] int
)

Create Table #3
(
	[uID] int,
	[Salesmanfirstname] varchar(100),
	[Salesmanlastname] varchar(100)
)
go

INSERT INTO #1 VALUES (1, 'Janitor Closet'), (2, 'Exterior Storage Room'), (3, 'Alarm Control Room'), (4, 'Sprinkler Backflow Room'),
 (5, 'Exterior Left Emergency Door'), (6, 'Main Entrance')
INSERT INTO #2 VALUES (1, 1, 1, 1), (2, 2, 1, 1), (3, 3, 1, 0), (4, 4, 1, 1), (5, 5, 1, 0), (6, 6, 1, 1), (7,1,2,1), (8,2,2,0), (9,3,2,1), (10,4,2,0)
INSERT INTO #3 VALUES (1, 'Big', 'Train'), (2, 'Miles', 'Davis')


;with CTE AS (SELECT #1.pName
,case when #2.accessgranted = 1 then 1 else 0 end As [Access Granted]
,case when #2.accessgranted = 0 then 0 else 0 end As [Access Denied]
,#3.Salesmanfirstname
,#3.Salesmanlastname
FROM #1
LEFT OUTER JOIN #2
ON #2.pID = #1.pID
INNER JOIN #3
ON #3.uID = #2.uID and #3.Salesmanlastname = 'Davis')
,CTE2 as (SELECT #1.pName
,case when #2.accessgranted = 1 then 1 else 0 end As [Access Granted]
,case when #2.accessgranted = 0 then 0 else 0 end As [Access Denied],null as FirstName,null as [LastName]
FROM #1
LEFT OUTER JOIN #2
ON #2.pID = #1.pID where #1.PName not in (select distinct Pname from CTE))

select * from CTE
Union all
select * from CTE2


DROP TABLE #1
DROP TABLE #2
DROP TABLE #3
Free Windows Admin Tool Kit Click here and download it now
May 21st, 2015 2:48pm
Thanks for trying to post DDL; most people do not. 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 constraints 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-

IWhy do think that giving  tables numbers for names makes any sense? But this crap is not a table! It has no keys. How many people do you know with a 200 character name? The USPS uses 20. Why invite garbage? 

Since we do not math on them, the identifiers cannot be numeric. What you are doing is faking pointers with INTEGERs because you have no idea what a key is! This is a common noob mistake, but I am not going to fix it. But you need to!

CREATE TABLE Locations
(location_id INTEGER NOT NULL PRIMARY KEY, -- wrong datatype
 location_name VARCHAR(100) NOT NULL);

INSERT INTO Locations 
VALUES 
(1, 'JANITOR CLOSET'), 
(2, 'EXTERIOR STORAGE ROOM'), 
(3, 'ALARM CONTROL ROOM'), 
(4, 'SPRINKLER BACKFLOW ROOM'), 
(5, 'EXTERIOR LEFT EMERGENCY DOOR'), 
(6, 'MAIN ENTRANCE');

CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRMARY KEY, -- wrong datatype
 emp_firstname VARCHAR(20) NOT NULL, 
 emp_lastname VARCHAR (20) NOT NULL);

INSERT INTO Personnel 
VALUES
(1, 'BIG', 'TRAIN'), 
(2, 'MILES', 'DAVIS');

Now we have a relationship table:
INSERT INTO Authorizations
VALUES 
(1, 1, 1), 
(1, 2, 1), 
(2, 1, 1), 
(2, 2, 0), 
(3, 1, 0), 
(3, 2, 1), 
(4, 1, 1), 
(4, 2, 0),
(5, 1, 0), 
(6, 1, 1), 


Your Authorizations table seems to be using a flag in SQL. We do not use flags in this language; there would be start and end dates for the credentials. We will ignore that for now. 

CREATE TABLE Authorizations 
(location_id INTEGER NOT NULL
  REFERENCES Locations(location_id), 
 emp_id INTEGER NOT NUL
  REFERENCES Personnel(emp_id), 
 PRIMARY KEY (location_id, emp_id ), 
 authorization_flg INTEGER DEFAULT 0 NOT NULL - 0= not allowed
  CHECK(authorization_flg IN (0, 1));

The DRI (do you know what that is?) will maintain the relationships. Your basic query will be:

SELECT L.location_name, P.emp_firstname, P.emp_lastname,
FROM Locations AS l,
     Authorizations AS A
     Personnel AS P
WHERE P.emp_id = A.emp_id 
  AND L.location_id = A.location_id;

Notice that I have an explicit not allowed value, as opposed to a created NULL from an outer join.  But we have no idea about your business rules. 

You can now write other queries for these conditions, using outer joins:
All the personnel and their authorizations
All the location and the personnel for them. 
May 21st, 2015 5:45pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics