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.

MS SQL SERVER 2005 BUG? Cannot insert NULL into column diagram_id

Hi friends,
when trying to save a diagram I got an error:
The sp_creatediagram procedure attempted to return a status of NULL, which is not allowed.
Whats with this??I had the same issue an just fixed it by turning the "diagram_id" field in SysDiagrams table to "identitity".

I dropped the table and ran the following script.

CREATE TABLE [dbo].[sysdiagrams](
[name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[principal_id] [int] NOT NULL,
[diagram_id] [int] identity(1,1),
[version] [int] NULL,
[definition] [varbinary](max) NULL
) ON [PRIMARY]

It worked.

MS SQL records pagination (LIMIT/OFSET)

What is best way to return 1st 20 records, then next 20 records, so on,
dynamically? (MySQL/pgSQL, others have limit/ofset).
Use co-related like this:
http://josephlindsay.com/archives/20...-ms-sql-server
http://msdn.microsoft.com/library/de...nethowto05.asp
Is there another better way ?
tia,
..V
"netsql" <netsql@.roomity.com> wrote in message
news:eI4KJytRGHA.1576@.tk2msftngp13.phx.gbl...
> What is best way to return 1st 20 records, then next 20 records, so on,
> dynamically? (MySQL/pgSQL, others have limit/ofset).
> Use co-related like this:
> http://josephlindsay.com/archives/20...-ms-sql-server
> http://msdn.microsoft.com/library/de...nethowto05.asp
> Is there another better way ?
> tia,
> .V
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Is there a good SQL answer (for use w/
DataSource/WinForms/DataGrid/SmartClient and not ASP)
like http://dev.mysql.com/doc/refman/5.0/en/select.html ?
..V
David Portas wrote:
> "netsql" <netsql@.roomity.com> wrote in message
> news:eI4KJytRGHA.1576@.tk2msftngp13.phx.gbl...
> http://www.aspfaq.com/show.asp?id=2120
>
|||"netsql" <netsql@.roomity.com> wrote in message
news:4415DE37.2060805@.roomity.com...
> Is there a good SQL answer (for use w/
> DataSource/WinForms/DataGrid/SmartClient and not ASP)
> like http://dev.mysql.com/doc/refman/5.0/en/select.html ?
>
Read the whole article. Six of those techniques are pure SQL and don't use
ASP.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

MS SQL records pagination (LIMIT/OFSET)

What is best way to return 1st 20 records, then next 20 records, so on,
dynamically? (MySQL/pgSQL, others have limit/ofset).
Use co-related like this:
http://josephlindsay.com/archives/2...n-ms-sql-server
http://msdn.microsoft.com/library/d...
ethowto05.asp
Is there another better way ?
tia,
.V"netsql" <netsql@.roomity.com> wrote in message
news:eI4KJytRGHA.1576@.tk2msftngp13.phx.gbl...
> What is best way to return 1st 20 records, then next 20 records, so on,
> dynamically? (MySQL/pgSQL, others have limit/ofset).
> Use co-related like this:
> http://josephlindsay.com/archives/2...enethowto05.asp
> Is there another better way ?
> tia,
> .V
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Is there a good SQL answer (for use w/
DataSource/WinForms/DataGrid/SmartClient and not ASP)
like http://dev.mysql.com/doc/refman/5.0/en/select.html ?
.V
David Portas wrote:
> "netsql" <netsql@.roomity.com> wrote in message
> news:eI4KJytRGHA.1576@.tk2msftngp13.phx.gbl...
> http://www.aspfaq.com/show.asp?id=2120
>|||"netsql" <netsql@.roomity.com> wrote in message
news:4415DE37.2060805@.roomity.com...
> Is there a good SQL answer (for use w/
> DataSource/WinForms/DataGrid/SmartClient and not ASP)
> like http://dev.mysql.com/doc/refman/5.0/en/select.html ?
>
Read the whole article. Six of those techniques are pure SQL and don't use
ASP.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

MS SQL records pagination (LIMIT/OFSET)

What is best way to return 1st 20 records, then next 20 records, so on,
dynamically? (MySQL/pgSQL, others have limit/ofset).
Use co-related like this:
http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenethowto05.asp
Is there another better way ?
tia,
.V"netsql" <netsql@.roomity.com> wrote in message
news:eI4KJytRGHA.1576@.tk2msftngp13.phx.gbl...
> What is best way to return 1st 20 records, then next 20 records, so on,
> dynamically? (MySQL/pgSQL, others have limit/ofset).
> Use co-related like this:
> http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenethowto05.asp
> Is there another better way ?
> tia,
> .V
http://www.aspfaq.com/show.asp?id=2120
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Is there a good SQL answer (for use w/
DataSource/WinForms/DataGrid/SmartClient and not ASP)
like http://dev.mysql.com/doc/refman/5.0/en/select.html ?
.V
David Portas wrote:
> "netsql" <netsql@.roomity.com> wrote in message
> news:eI4KJytRGHA.1576@.tk2msftngp13.phx.gbl...
>> What is best way to return 1st 20 records, then next 20 records, so on,
>> dynamically? (MySQL/pgSQL, others have limit/ofset).
>> Use co-related like this:
>> http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenethowto05.asp
>> Is there another better way ?
>> tia,
>> .V
> http://www.aspfaq.com/show.asp?id=2120
>|||"netsql" <netsql@.roomity.com> wrote in message
news:4415DE37.2060805@.roomity.com...
> Is there a good SQL answer (for use w/
> DataSource/WinForms/DataGrid/SmartClient and not ASP)
> like http://dev.mysql.com/doc/refman/5.0/en/select.html ?
>
Read the whole article. Six of those techniques are pure SQL and don't use
ASP.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

MS SQL performance from 10" to 3 minutes

Hello all !

I am runing from .NET application an SQL Query
it normally return the rows in 10 seconds
but time to time the application turn 2 or 3 minutes and nearlly crash (or crash)

with exactly the same datas in database

what can be the reasons ?

thank youcheck whether the session is getting expired or not if not kill it.|||other reason could be lock put on the table during the transaction which may keep the DB server busy.
Also check if some other query seeks a large resultset from DB.|||ppavan21 if I kill the session and a user is logged-in he will be thrown, I cannot do it , or do you see a solution ?

wash : is there a way to unlock ? ot what can I do ?

on 5 rows it takes normally less than one second, sometimes it can turn a few minutes and crash with exactly the sames datas

thank you|||the reasons for this can vary widely.

things to check...

1. open up the task manager to see if it is the sqlserver process consuming resources. Are you running anything on the machine? IIS? exchange?

2. run sp_who\sp_who2\sp_lock to look for blocking\resource intensive operations or excessive locking.

3. Open up the performance monitor and make sure you disk que length is under 3.

4. Have you looked at the execution plan of the query that varies in execution time? Are there any table\index scan as opposed to index seeks in the plan? If the query can return vastly varying amounts of data, have you tried adding WITH RECOMPILE to the query? Have you recompiled the stored procedure lately? Are the indexes that the query is using heavily fragmented?

That should keep you busy.|||RECOMPILE ? i didn't know it was even possible
how do yo do it ?|||recompiling is sometimes beneficial if there has been a large amount of data added to your database recently which can have the effect of making your execution plan out of date.

see sp_recompile in Books Online.|||Sean,

I believe you assume That this is a sproc

I got Money that it's not|||oh probably not. dude can probably use a little BOL reading anyways.

MS SQL Function return string - what am I doing wrong?

Cannot see where I am going wrong. I always get a value of 0. I know my function works correctly, so it must be the VB.

CREATE FUNCTION [dbo].[getNextProjectID] ()
RETURNS varchar(10) AS
BEGIN
''.....................
DECLARE @.vNextProjectID varchar(10)
RETURN @.vNextProjectID
END

Sub LoadNextProjectNumber()
Dim vProjectID As String
Dim cmd As New SqlClient.SqlCommand()
cmd.Connection = sqlConn
cmd.CommandText = "getNextProjectID"
cmd.Parameters.Add("@.vNextProjectID", vProjectID)
cmd.Parameters("@.vNextProjectID").Direction = ParameterDirection.ReturnValue
cmd.ExecuteScalar()
vProjectID = cmd.Parameters("@.vNextProjectID").Value
txtProjectID.Text = vProjectID
cmd.Dispose()
End Sub
Hi Jagdipa,
Are you sure that your function executes correctly? I can run your example and get back string successfully. Try to run Profiler in MS SQL to see function execution log.|||check ifthis articlehelps|||hello..
try to use :
cmd.CommandType = storedprocedure
and
cmd.ExecuteNonQuery|||Thanks guys.
All I had to do was write the following line (thanks busyweb):
cmd.commandType = storedProcedure
Dont know why I need that line when the sql is a function and not astored procedure. I have had this code work before without that line.But, as long as it works...
Jagdip