I want to show all possible values from my #Access Table, but I can't get the syntax down.....

Create Table #Users
(
	userID int,
	firstname text,
	lastname text,
	hiredate datetime
)

Create Table #GrantedAccess
(
	userID int,
	accessID int,
	accessgranted int
)

Create Table #Access
(
	accessID int,
	accessName text
)

INSERT INTO #Users VALUES (1, 'A', 'B', '2015-05-01'), (2, 'C', 'D', '2014-05-01'), (3, 'E', 'F', '2010-05-01')
INSERT INTO #GrantedAccess VALUES (1, 1, 'First'), (1, 2, 'Second'), (1, 5, 'Fifth'), (2, 1, 'First'), (3, 8, 'Eigth'), (3, 10, 'Ten')
INSERT INTO #Access VALUES (1, 'First'), (2, 'Second'), (3, 'Third'), (4, 'Fourth'), (5, 'Fifth'), (6, 'Sixth'), (7, 'Seventh'), (8, 'Eighth'), (9, 'Ninth'), (10, 'Tenth')


SELECT ac.accessName,us.FirstName, us.LastName  
FROM #Access ac
INNER JOIN #GrantedAccess ga
ON ac.accessID = ga.accessID
INNER JOIN #Users us
ON us.userID = ga.userID

• Edited by Baby_Christopher 14 hours 44 minutes ago

Hi Christopher,

Thank you for the DDL, there is 1 mistake in the data type in grantedaccess. It would also help if you made a sample of the output you expect so we know what we are shooting for. Here is an update that might be what you want (with updated DDL) but I am not sure. If it's off please post a table with data that you expect from the output.

Create Table #Users
(
	userID int,
	firstname text,
	lastname text,
	hiredate datetime
)

Create Table #GrantedAccess
(
	userID int,
	accessID int,
	accessgranted varchar(50)
)

Create Table #Access
(
	accessID int,
	accessName text
)
go

INSERT INTO #Users VALUES (1, 'A', 'B', '2015-05-01'), (2, 'C', 'D', '2014-05-01'), (3, 'E', 'F', '2010-05-01')
INSERT INTO #GrantedAccess (userid, accessid, accessgranted) VALUES (1, 1, 'First'), (1, 2, 'Second'), (1, 5, 'Fifth'), (2, 1, 'First'), (3, 8, 'Eigth'), (3, 10, 'Ten')
INSERT INTO #Access VALUES (1, 'First'), (2, 'Second'), (3, 'Third'), (4, 'Fourth'), (5, 'Fifth'), (6, 'Sixth'), (7, 'Seventh'), (8, 'Eighth'), (9, 'Ninth'), (10, 'Tenth')


SELECT ac.accessName,us.FirstName, us.LastName  
FROM #Access ac
LEFT OUTER JOIN #GrantedAccess ga
ON ga.accessID = ac.accessID
LEFT OUTER JOIN #Users us
ON ga.userID = us.userID

That is it exactly!!! Thank you IWolbers, sorry for my error in the DDL I was trying my best to provide all required information.

Good day,

as IWolbers posted, I am guessing that you want to get all the rows from the #Access but you dont. If this is the case that this is because you use INNER JOIN which remove any row that is not in the other table that you joined to the #Access table. You probably need to use LEFT JOIN

You can read more and learn about the different JOIN options here:

http://www.w3schools.com/sql/sql_join.a

Looks like I just threw a wrench in the whole query (or at least for me)...what if I need to add a where clause based off lastname (or firstname)  If I alter the select statement to the below, it removes all null values?

SELECT ac.accessName,us.FirstName, us.LastName  
FROM #Access ac
LEFT OUTER JOIN #GrantedAccess ga
ON ga.accessID = ac.accessID
LEFT OUTER JOIN #Users us
ON ga.userID = us.userID
WHERE CONVERT(VARCHAR(MAX),us.lastname) = 'B'

Looks like I just threw a wrench in the whole query (or at least for me)...what if I need to add a where clause based off lastname (or firstname)  If I alter the select statement to the below, it removes all null values?

SELECT ac.accessName,us.FirstName, us.LastName  
FROM #Access ac
LEFT OUTER JOIN #GrantedAccess ga
ON ga.accessID = ac.accessID
LEFT OUTER JOIN #Users us
ON ga.userID = us.userID
WHERE CONVERT(VARCHAR(MAX),us.lastname) = 'B'

Adding up a where clause here will give you the subset of data from your joins. So, you will only get rows which has lastname as 'B' ; so there won't be any null for last name as you have mentioned last name should be = 'b'

Hope this will help... let me know if I misunderstood your question

Also, you can always take help of below chart to write your query:

Looks like I just threw a wrench in the whole query (or at least for me)...what if I need to add a where clause based off lastname (or firstname)  If I alter the select statement to the below, it removes all null values?

SELECT ac.accessName,us.FirstName, us.LastName  
FROM #Access ac
LEFT OUTER JOIN #GrantedAccess ga
ON ga.accessID = ac.accessID
LEFT OUTER JOIN #Users us
ON ga.userID = us.userID
WHERE CONVERT(VARCHAR(MAX),us.lastname) = 'B'

Adding up a where clause here will give you the subset of data from your joins. So, you will only get rows which has lastname as 'B' ; so there won't be any null for last name as you have mentioned last name should be = 'b'

Hope this will help... let me know if I misunderstood your question

How could I still show the full set returned w/o the Where clause but with the where clause added?  Does that make sense what I am asking?  For example, I want the query with the where clause to return the result set below

First	A	B
First	NULL	NULL
Second	A	B
Third	NULL	NULL
Fourth	NULL	NULL
Fifth	A	B
Sixth	NULL	NULL
Seventh	NULL	NULL
Eighth	NULL	NULL
Ninth	NULL	NULL
Tenth	NULL	NULL

However, throwing in the where clause only returns this result set

First	A	B
Second	A	B
Fifth	A	B

JOIN can be very heavy and expansive operation. In most small cases (like 2 joins) the SQL Server Query optimize engine do a good job, but it is highly recomend to make sure that you filter the data as soon as you can BEFORE the JOIN operation. a simple way is to use sub queries like this:

SELECT ac.accessName,us.FirstName, us.LastName  
FROM #Access ac
LEFT OUTER JOIN #GrantedAccess ga
ON ga.accessID = ac.accessID
INNER OUTER JOIN ( select * from #Users WHERE CONVERT(VARCHAR(MAX),us.lastname) = 'B') us
ON ga.userID = us.userID

Now I changed it into INNER join so you will get only results that fit the filter in the #Users table. But now I will not get all the accessName table values. In this solution I will not get any NULL since it is not LEFT JOIN. Again! you should learn the basic idea of JOINs :-)

If you want to get the null then leave it LEFT JOIN.

* You can filter the data after the JOIN as well (not recommended for most cases if you can do it before)

SELECT ac.accessName,us.FirstName, us.LastName  
FROM #Access ac
LEFT OUTER JOIN #GrantedAccess ga
ON ga.accessID = ac.accessID
LEFT OUTER JOIN #Users us ON ga.userID = us.userID  and CONVERT(VARCHAR(MAX),us.lastname) = 'B'

Put the filter (originally in your WHERE clause) in the FROM clause like this.

Edit: corrected description below code