MS SQL Server Return 1 if exists?

I'm trying to return data from a query and I can't even begin to wrap my head around what I need to do to get started.

I have two tables:

TableOne
col_deptName
col_deptID

TableTwo
col_userName
col_userid
col_permissionDeptName

I want to return all rows in TableOne and 1 or 0 in based on whether TableTwo.col_permissionDeptName = TableOne.col_deptName

Such that the output would look like:

"Department" "Enabled"
------ ------
dept1 0
dept2 1
dept3 0
dept4 1
dept5 1
dept6 1

Quote:

Originally Posted by jinksto

I'm trying to return data from a query and I can't even begin to wrap my head around what I need to do to get started.

I have two tables:

TableOne
col_deptName
col_deptID

TableTwo
col_userName
col_userid
col_permissionDeptName

I want to return all rows in TableOne and 1 or 0 in based on whether TableTwo.col_permissionDeptName = TableOne.col_deptName

Such that the output would look like:

"Department" "Enabled"
------ ------
dept1 0
dept2 1
dept3 0
dept4 1
dept5 1
dept6 1

Try this...

SELECT col_deptID AS DEPTID,col_deptName AS DEPARTMENT,
CASE ISNULL(col_permissionDeptName,'') WHEN '' THEN 0
ELSE 1 END AS ENABLED
FROM TableOne LEFT JOIN TableTwo
ON col_deptName = col_permissionDeptName|||

Quote:

Originally Posted by vijaii

Try this...

SELECT col_deptID AS DEPTID,col_deptName AS DEPARTMENT,
CASE ISNULL(col_permissionDeptName,'') WHEN '' THEN 0
ELSE 1 END AS ENABLED
FROM TableOne LEFT JOIN TableTwo
ON col_deptName = col_permissionDeptName

That almost works but unfortunately col_permissionDeptName will never be null.

TableTwo
col_userName
col_userid
col_permissionDeptName

Table two rows look like this:

col_userName col_userid col_permissionDeptName
userJoe user1 dept1
userbob user2 dept1
userbob user2 dept2
usermike user3 dept1
usermike user3 dept3

... so this is just a list of users and departments that they have access to and doesn't have the complete list of departments.|||

Quote:

Originally Posted by jinksto

That almost works but unfortunately col_permissionDeptName will never be null.

TableTwo
col_userName
col_userid
col_permissionDeptName

Table two rows look like this:

col_userName col_userid col_permissionDeptName
userJoe user1 dept1
userbob user2 dept1
userbob user2 dept2
usermike user3 dept1
usermike user3 dept3

... so this is just a list of users and departments that they have access to and doesn't have the complete list of departments.

I used left join so if there is dept name in Tableone and not in tabletwo then the
permissionDeptName in tabletwo will be null.If you are not clear about this please refer the Left Join in the SQL help|||

Quote:

Originally Posted by vijaii

I used left join so if there is dept name in Tableone and not in tabletwo then the
permissionDeptName in tabletwo will be null.If you are not clear about this please refer the Left Join in the SQL help

You're right, of course. Sorry about that, I misread it.

Unfortunately, I think I explained what I wanted incorrectly.

I want to return the complete list by T2.col_userid with a 1 or a 0 to indicate which departments

So, essentially, for user1 I want to return a list of all departments along with a 1 or a 0 indicating whether T2.col_permission exists for user1

Sorry if I'm not explaining what I want correct. Thanks for your help.|||

Quote:

Originally Posted by jinksto

You're right, of course. Sorry about that, I misread it.

Unfortunately, I think I explained what I wanted incorrectly.

I want to return the complete list by T2.col_userid with a 1 or a 0 to indicate which departments

So, essentially, for user1 I want to return a list of all departments along with a 1 or a 0 indicating whether T2.col_permission exists for user1

Sorry if I'm not explaining what I want correct. Thanks for your help.

Just do what Vijaii said but with a WHERE col_userid = 'user1'|||

Quote:

Originally Posted by DonlonP

Just do what Vijaii said but with a WHERE col_userid = 'user1'

If I do that then it only returns values from the left table where there's an equivalent right table entry... So I only ever get 1's and no 0's

I want all values from left table and 1 if an equivalent value exists in right table and 0 if null.|||

Quote:

Originally Posted by jinksto

If I do that then it only returns values from the left table where there's an equivalent right table entry... So I only ever get 1's and no 0's

I want all values from left table and 1 if an equivalent value exists in right table and 0 if null.

Can you give an example with data for TableOne,TableTwo and the Final Result
i.e. what should be the outcome Result by joining TableOne and TableTwo? so that I can help you better.