Help with an WHERE NOT EXISTS subquery please.

Hi, I have 2 tables:

tblStations StationID
Station

tblStationUser RecordID
UserName
Station

I'm trying to come up with a dataset that contains the
tblStations.Station
EXCEPT for where that Station exists in tblStationUser where the
UserName = @.varUserName.

I've tried this but get 0 rows (I should get about 40):

SELECT tblStations.Station
FROM tblStations
WHERE NOT EXISTS
(SELECT tblStationUser.Station FROM tblStationUser WHERE
tblStationUser.UserName=@.varUserName)
ORDER BY Station

I tried the subquery separately which returns the correct number of
rows.

Any clues as to where I'm going wrong?

Thanks!
KathyKathy,

You forgot to join tables. Try one of these queries. I haven't tested them.

SELECT Station
FROM tblStations
WHERE NOT EXISTS (SELECT *
FROM tblStationUser
WHERE tblStationUser.Station = tblStations.Station
AND tblStationUser.UserName = @.varUserName)
ORDER BY Station

OR

SELECT Station
FROM tblStations
WHERE Station NOT IN (SELECT Station
FROM tblStationUser
WHERE UserName = @.varUserName)
ORDER BY Station

Good luck,
Shervin

"KathyB" <KathyBurke40@.attbi.com> wrote in message
news:75e8d381.0310160945.2b2df7e4@.posting.google.c om...
> Hi, I have 2 tables:
> tblStations StationID
> Station
> tblStationUser RecordID
> UserName
> Station
> I'm trying to come up with a dataset that contains the
> tblStations.Station
> EXCEPT for where that Station exists in tblStationUser where the
> UserName = @.varUserName.
> I've tried this but get 0 rows (I should get about 40):
> SELECT tblStations.Station
> FROM tblStations
> WHERE NOT EXISTS
> (SELECT tblStationUser.Station FROM tblStationUser WHERE
> tblStationUser.UserName=@.varUserName)
> ORDER BY Station
> I tried the subquery separately which returns the correct number of
> rows.
> Any clues as to where I'm going wrong?
> Thanks!
> Kathy|||Thanks again Shervin. I wish I knew I fraction of what you do!

Thanks, Kat

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You are welcome Kathy, I'm hanging around here to learn more about SQL
Server. :-)

Shervin

"Kathy Burke" <kathyburke40@.comcast.net> wrote in message
news:3f8fd569$0$193$75868355@.news.frii.net...
> Thanks again Shervin. I wish I knew I fraction of what you do!
>
> Thanks, Kat
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

help with a 'simple' sql-statement

Hi there,
i have the following problem: in a database i have to detect if a certain
column in a certain table exists. I
use the function 'ColumnAlreadyExists'. If not i have to create that column
and then i have to fill this
column. This is my code:
****************************************
*********
-- Function to check whether a specific column exists in a table
CREATE FUNCTION ColumnAlreadyExists(@.TableName NVARCHAR(128),@.ColumnName
NVARCHAR(128))
RETURNS INTEGER --Returns 0 if column does not exist. Returns 1 if column
exists.
AS
BEGIN
--See if the Table already contains the column.
IF EXISTS
(SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
WHERE ObjectProperty(O.ID,'IsUserTable')=1
AND O.Name=@.TableName
AND C.Name=@.ColumnName)
RETURN 1
--Table does not contain the column.
RETURN 0
END
GO
-- Add column DataId to table Data if necessary
IF .dbo.ColumnAlreadyExists('data','dataid')=0
BEGIN
ALTER TABLE [data]
ADD [dataid] [int] NULL
-- Fill column DataId for each row in the Data table
DECLARE @.index1 int, @.index2 int, @.datapk int
DECLARE DataID_Cursor CURSOR FOR
SELECT index1, index2, datapk
FROM data
OPEN DataID_Cursor
FETCH NEXT FROM DataID_Cursor
INTO @.index1, @.index2, @.datapk
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE Data
set dataid = ( index1 * index2 )
WHERE datapk = @.datapk
FETCH NEXT FROM DataID_Cursor
INTO @.index1, @.index2, @.datapk
END
END
DROP FUNCTION ColumnAlreadyExists
****************************************
*********
The problem now is that when i want to fill the dataid column it does not
exists yet, cause there was no GO
yet. But when i put a GO between the creating of the column and the filling
of this column i don't know
anymore whether the column existed or not. So my question is: How can i do
the following:
IF .dbo.ColumnAlreadyExists('data','dataid')=0
BEGIN
ALTER TABLE [data]
ADD [dataid] [int] NULL
-- Fill the just created column dataid for each row in the Data table
END
Can anyone help me with this problem?
thanks,
Koert"Koert" <Koert@.discussions.microsoft.com> wrote in message
news:BD268A25-ABFF-4287-B76D-F39A8EB954D9@.microsoft.com...

> Can anyone help me with this problem?
Maybe DECLARE a variable...?|||I already tried that but I can't use that variable after the GO to create th
e
column. I tried:
DECLARE @.result int
SELECT @.result = .dbo.ColumnAlreadyExists('data','dataid')
IF @.result = 0
BEGIN
ALTER TABLE [dta]
ADD [dataid][int] NULL
END
GO
if @.result = 0 <--This one is not declared anymore
BEGIN
--Fill column DataID
END
so this does not work or is there any other way to declare a kind of global
variable'
thanks,
Koert
"Mark Rae" wrote:

> "Koert" <Koert@.discussions.microsoft.com> wrote in message
> news:BD268A25-ABFF-4287-B76D-F39A8EB954D9@.microsoft.com...
>
> Maybe DECLARE a variable...?
>
>|||"Koert" <Koert@.discussions.microsoft.com> wrote in message
news:69895818-6900-4683-B2F5-E85949E48FBF@.microsoft.com...

> so this does not work or is there any other way to declare a kind of
> global
> variable'
Hmm - loathe though I am to suggest it, I think your only option might be to
create a temporary table... Temporary tables are persistent within a
connection, so should survive the GO statement...|||i thought about that to, it seemed to be so easy... The only other way i can
think of is to check if the dataid column is filled in, if not fill it.
thanks for posting,
Koert
"Mark Rae" wrote:

> "Koert" <Koert@.discussions.microsoft.com> wrote in message
> news:69895818-6900-4683-B2F5-E85949E48FBF@.microsoft.com...
>
> Hmm - loathe though I am to suggest it, I think your only option might be
to
> create a temporary table... Temporary tables are persistent within a
> connection, so should survive the GO statement...
>
>

Help with a Query

Using MS SQL 2000
I will try and post all relevant information.
--
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblAsset]
GO
CREATE TABLE [dbo].[tblAsset] (
[AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[AssetTypeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatelastScanned] [smalldatetime] NULL ,
[NextScanDate] [smalldatetime] NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
--
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblAssetOrgNode]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblAssetOrgNode]
GO
CREATE TABLE [dbo].[tblAssetOrgNode] (
[AssetID] [int] NOT NULL ,
[OrgSystemID] [int] NOT NULL ,
[OrgNodeID] [int] NOT NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
--
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblScan]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblScan]
GO
CREATE TABLE [dbo].[tblScan] (
[ScanID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[AssetID] [int] NULL ,
[ScanDate] [smalldatetime] NULL ,
[AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NOT NULL
) ON [PRIMARY]
GO
--
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblOrgSystem]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblOrgSystem]
GO
CREATE TABLE [dbo].[tblOrgSystem] (
[OrgSystemID] [int] NOT NULL ,
[OrgSystem] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[OrgSystemDescr] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblOrgSystemNode]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblOrgSystemNode]
GO
CREATE TABLE [dbo].[tblOrgSystemNode] (
[OrgSystemID] [int] NOT NULL ,
[OrgNodeID] [int] NOT NULL ,
[OrgNode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
--
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblOrgSystemNodeParent]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblOrgSystemNodeParent]
GO
CREATE TABLE [dbo].[tblOrgSystemNodeParent] (
[OrgSystemID] [int] NOT NULL ,
[OrgNodeID] [int] NOT NULL ,
[OrgNodeParentID] [int] NOT NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
----
--
Here is a quick sample from each table...
tblAsset
AssetID AssetName MAC DateLastScanned
1 RyanPC 00:00:00:00:00:00 6/20/06
--
tblAssetOrgNode
AssetID OrgSystemID OrgNodeID
1 1 3
--
tblScan
ScanID AssetID ScanDate AssetName MAC
1 1 6/20/06 RyanPC x
--
tblOrgSystem
OrgSystemID OrgSystem
1 NorthAmerican
2 Canada
3 Europe
--
tblOrgSystemNode
OrgSystemID OrgNodeID OrgNode
1 3 Mano
1 4 Houston
1 7 Mano-sales
1 10 Houston-sales
2 5 Toronto
2 6 Ontario
2 13 Toronto-sales
2 16 Ontario-sales
3 31 Amsterdam
--
tblOrgSystemNodeParent
OrgSystemId OrgNodeID OrgNodeParentID
1 3 3
1 4 4
1 7 3
1 10 4
2 5 5
2 6 6
2 13 5
2 16 6
3 31 31
(nodes who's parents are themselves are that way for organization/query
purposes)
I am working with a treeview, and I go through the organization
strucute and have an "Asset" Node that is created as a child of each
Parent.
The way my project is RIGHT NOW the user clicks on the Asset Node to
display the Assets assigned to the Parent via this select statement.
select AssetName from tblAsset JOIN tblAssetOrgNode ON tblAsset.AssetID
= tblAssetOrgNode.AssetID where OrgNodeID = 'assetNodeID'
assetNodeID is determined in my app code.
These assets are displayed in a listview box. When one of those assets
are clicked it displays the relevant information in a gridview using
this select statement.
select AssetID, AssetName, MAC as 'MAC(if exists)', ScanID as
'ScanID(if exists)', ScanDate from tblScan
What I want to be able to do is illimate the middle man. I want the
user to be able to click on the "Asset" node that and display the
relevant information only for the Assets who's Parent is what the user
selected.
Any help on this one would be greatly appreciated.
If any more inforation is required, perhaps an explanation of the
structure (even though I posted more than enough info) I would be glad
to clear things up. Thanks in advance.This problem I have resolved myself, thanks to anyone who took the time
to take a look at it, I have another post in regards to Update then
Insert after Update statement that I could definately use some help on.
Thanks again.
rhaazy wrote:
> Using MS SQL 2000
> I will try and post all relevant information.
> --
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[tblAsset]
> GO
> CREATE TABLE [dbo].[tblAsset] (
> [AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [AssetTypeID] [int] NULL ,
> [MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DatelastScanned] [smalldatetime] NULL ,
> [NextScanDate] [smalldatetime] NULL ,
> [DateCreated] [smalldatetime] NULL ,
> [LastModified] [smalldatetime] NULL ,
> [Deleted] [bit] NULL
> ) ON [PRIMARY]
> GO
> --
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblAssetOrgNode]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblAssetOrgNode]
> GO
> CREATE TABLE [dbo].[tblAssetOrgNode] (
> [AssetID] [int] NOT NULL ,
> [OrgSystemID] [int] NOT NULL ,
> [OrgNodeID] [int] NOT NULL ,
> [DateCreated] [datetime] NULL ,
> [LastModified] [datetime] NULL ,
> [Deleted] [bit] NULL
> ) ON [PRIMARY]
> GO
> --
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblScan]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[tblScan]
> GO
> CREATE TABLE [dbo].[tblScan] (
> [ScanID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [AssetID] [int] NULL ,
> [ScanDate] [smalldatetime] NULL ,
> [AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateCreated] [smalldatetime] NULL ,
> [LastModified] [smalldatetime] NULL ,
> [Deleted] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> --
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblOrgSystem]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblOrgSystem]
> GO
> CREATE TABLE [dbo].[tblOrgSystem] (
> [OrgSystemID] [int] NOT NULL ,
> [OrgSystem] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [OrgSystemDescr] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateCreated] [datetime] NULL ,
> [LastModified] [datetime] NULL ,
> [Deleted] [bit] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> --
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblOrgSystemNode]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblOrgSystemNode]
> GO
> CREATE TABLE [dbo].[tblOrgSystemNode] (
> [OrgSystemID] [int] NOT NULL ,
> [OrgNodeID] [int] NOT NULL ,
> [OrgNode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateCreated] [datetime] NULL ,
> [LastModified] [datetime] NULL ,
> [Deleted] [bit] NULL
> ) ON [PRIMARY]
> GO
> --
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblOrgSystemNodeParent]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblOrgSystemNodeParent]
> GO
> CREATE TABLE [dbo].[tblOrgSystemNodeParent] (
> [OrgSystemID] [int] NOT NULL ,
> [OrgNodeID] [int] NOT NULL ,
> [OrgNodeParentID] [int] NOT NULL ,
> [DateCreated] [datetime] NULL ,
> [LastModified] [datetime] NULL ,
> [Deleted] [bit] NULL
> ) ON [PRIMARY]
> GO
> ----
--
> Here is a quick sample from each table...
> tblAsset
> AssetID AssetName MAC DateLastScanned
> 1 RyanPC 00:00:00:00:00:00 6/20/06
> --
> tblAssetOrgNode
> AssetID OrgSystemID OrgNodeID
> 1 1 3
> --
> tblScan
> ScanID AssetID ScanDate AssetName MAC
> 1 1 6/20/06 RyanPC x
> --
> tblOrgSystem
> OrgSystemID OrgSystem
> 1 NorthAmerican
> 2 Canada
> 3 Europe
> --
> tblOrgSystemNode
> OrgSystemID OrgNodeID OrgNode
> 1 3 Mano
> 1 4 Houston
> 1 7 Mano-sales
> 1 10 Houston-sales
> 2 5 Toronto
> 2 6 Ontario
> 2 13 Toronto-sales
> 2 16 Ontario-sales
> 3 31 Amsterdam
> --
> tblOrgSystemNodeParent
> OrgSystemId OrgNodeID OrgNodeParentID
> 1 3 3
> 1 4 4
> 1 7 3
> 1 10 4
> 2 5 5
> 2 6 6
> 2 13 5
> 2 16 6
> 3 31 31
> (nodes who's parents are themselves are that way for organization/query
> purposes)
> I am working with a treeview, and I go through the organization
> strucute and have an "Asset" Node that is created as a child of each
> Parent.
> The way my project is RIGHT NOW the user clicks on the Asset Node to
> display the Assets assigned to the Parent via this select statement.
> select AssetName from tblAsset JOIN tblAssetOrgNode ON tblAsset.AssetID
> = tblAssetOrgNode.AssetID where OrgNodeID = 'assetNodeID'
> assetNodeID is determined in my app code.
> These assets are displayed in a listview box. When one of those assets
> are clicked it displays the relevant information in a gridview using
> this select statement.
> select AssetID, AssetName, MAC as 'MAC(if exists)', ScanID as
> 'ScanID(if exists)', ScanDate from tblScan
> What I want to be able to do is illimate the middle man. I want the
> user to be able to click on the "Asset" node that and display the
> relevant information only for the Assets who's Parent is what the user
> selected.
> Any help on this one would be greatly appreciated.
> If any more inforation is required, perhaps an explanation of the
> structure (even though I posted more than enough info) I would be glad
> to clear things up. Thanks in advance.

Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need some
help.
I am writting the procedure to check if multiple records exists and the only
way I can figure it out is to use @.@.RowCount, but can't get the right result,
please help.
What I have now is
if exists(select c_driver from cartons where orderid = @.orderid and
@.@.Rowcount = 1)
update...
I tried using the following but got an error in my vb application becuase
the Procedure was returning rows
select c_driver from cartons where orderid = @.orderid
if @.@.Rowcount = 1
update...
Is there any way to use the above query without returning rows to VB?
Is there a way to write an exists to query @.@.Rowcount?
What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
the last operation... I have a feeling you really want:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
update ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> I am writing a vb.net application that calls a stored procedure and need
some
> help.
> I am writting the procedure to check if multiple records exists and the
only
> way I can figure it out is to use @.@.RowCount, but can't get the right
result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>
|||I assume you want to update the row if it already exists and insert the row
if it doesn't exist. If so this should work...
UPDATE cartons SET
...
WHERE orderid = @.orderid
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO cartons (...) VALUES (...)
END
Hope this helps.
Paul
"John Shepherd" wrote:

> I am writing a vb.net application that calls a stored procedure and need some
> help.
> I am writting the procedure to check if multiple records exists and the only
> way I can figure it out is to use @.@.RowCount, but can't get the right result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>
|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Adam Machanic" wrote:

> What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
> the last operation... I have a feeling you really want:
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> update ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> some
> only
> result,
>
>
|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Paul" wrote:
[vbcol=seagreen]
> I assume you want to update the row if it already exists and insert the row
> if it doesn't exist. If so this should work...
> UPDATE cartons SET
> ...
> WHERE orderid = @.orderid
> IF (@.@.ROWCOUNT = 0)
> BEGIN
> INSERT INTO cartons (...) VALUES (...)
> END
> Hope this helps.
> Paul
> "John Shepherd" wrote:
|||Again, the COUNT(*) should do exactly what you need here... can you tell me
why this won't work for you:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
BEGIN
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
END
ELSE
BEGIN
-- do something else...
END
... Given that, however, I should ask why you're updating your orders table
with the driver from the cartons table? Why denormalize your data like
that?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> I tried the count() but I don't get what I need
> what I need to do is determin is if a single driver is assigned to
multiple
> cartons within an order. for Instance say driver #1 was assigned to Carton
> 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
these
> cartons are in Order #1000
> Order# Carton# Driver#
> 1000 100 1
> 1000 101 24
> 1000 102 24
> What I need to get to is this without the select (becuase doing it this
way
> returns an error in my vb.net application because the select wants to
return[vbcol=seagreen]
> rows)
> select c_driver from Cartons where orderid = @.orderid
> if @.@.RowCount = 1
> --Only one driver exists for this order
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> if @.@.RowCount > 1
> -- Multiple Driver exists for this Order
>
> "Adam Machanic" wrote:
of[vbcol=seagreen]
message[vbcol=seagreen]
need[vbcol=seagreen]
the[vbcol=seagreen]
becuase[vbcol=seagreen]
|||The problem with count() is that for my example would return 3 rows, I can
only update when all cartons in the orders have the same driver#.
I left out the DISTINCT in my example it should be:
select DISTINCT c_driver from Cartons where orderid = @.orderid
If all drivers are the same for the cartons in the Order I should have 1
row, if there a seperate drivers I should have more than 1 row
The way the db is set up is to have one row for an order in the Orders
Table, ? Rows in Cartons table joined together on Orders.orderid =
Cartons.Orderid based on the #of cartons shipped with the order. All of the
pricing and driver commissions are based on the total $'s charged from the
Orders table. So I need to update the orders table with the correct driver
information, but only if the driver is the same. Little round about I know.
"Adam Machanic" wrote:

> Again, the COUNT(*) should do exactly what you need here... can you tell me
> why this won't work for you:
>
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> BEGIN
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> END
> ELSE
> BEGIN
> -- do something else...
> END
>
> ... Given that, however, I should ask why you're updating your orders table
> with the driver from the cartons table? Why denormalize your data like
> that?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> multiple
> these
> way
> return
> of
> message
> need
> the
> becuase
>
>
|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
[vbcol=seagreen]
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I know.
>
>
> "Adam Machanic" wrote:
|||Why don't you try COUNT(DISTINCT c_driver) ?
Rather than storing the data in two places (which has data integrity
implications), have you considered creating a view that will return the data
in the way you need it for reporting?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:871ECA2C-4846-4442-83FE-53C1657A3159@.microsoft.com...
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of
the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I
know.
>

Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need som
e
help.
I am writting the procedure to check if multiple records exists and the only
way I can figure it out is to use @.@.RowCount, but can't get the right result
,
please help.
What I have now is
if exists(select c_driver from cartons where orderid = @.orderid and
@.@.Rowcount = 1)
update...
I tried using the following but got an error in my vb application becuase
the Procedure was returning rows
select c_driver from cartons where orderid = @.orderid
if @.@.Rowcount = 1
update...
Is there any way to use the above query without returning rows to VB?
Is there a way to write an exists to query @.@.Rowcount?I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of thes
e
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Adam Machanic" wrote:

> What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
> the last operation... I have a feeling you really want:
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> update ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> some
> only
> result,
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of thes
e
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Paul" wrote:
[vbcol=seagreen]
> I assume you want to update the row if it already exists and insert the ro
w
> if it doesn't exist. If so this should work...
> UPDATE cartons SET
> ...
> WHERE orderid = @.orderid
> IF (@.@.ROWCOUNT = 0)
> BEGIN
> INSERT INTO cartons (...) VALUES (...)
> END
> Hope this helps.
> Paul
> "John Shepherd" wrote:
>|||Again, the COUNT(*) should do exactly what you need here... can you tell me
why this won't work for you:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
BEGIN
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
END
ELSE
BEGIN
-- do something else...
END
... Given that, however, I should ask why you're updating your orders table
with the driver from the cartons table? Why denormalize your data like
that?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> I tried the count() but I don't get what I need
> what I need to do is determin is if a single driver is assigned to
multiple
> cartons within an order. for Instance say driver #1 was assigned to Carton
> 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
these
> cartons are in Order #1000
> Order# Carton# Driver#
> 1000 100 1
> 1000 101 24
> 1000 102 24
> What I need to get to is this without the select (becuase doing it this
way
> returns an error in my vb.net application because the select wants to
return[vbcol=seagreen]
> rows)
> select c_driver from Cartons where orderid = @.orderid
> if @.@.RowCount = 1
> --Only one driver exists for this order
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> if @.@.RowCount > 1
> -- Multiple Driver exists for this Order
>
> "Adam Machanic" wrote:
>
of[vbcol=seagreen]
message[vbcol=seagreen]
need[vbcol=seagreen]
the[vbcol=seagreen]
becuase[vbcol=seagreen]|||The problem with count() is that for my example would return 3 rows, I can
only update when all cartons in the orders have the same driver#.
I left out the DISTINCT in my example it should be:
select DISTINCT c_driver from Cartons where orderid = @.orderid
If all drivers are the same for the cartons in the Order I should have 1
row, if there a seperate drivers I should have more than 1 row
The way the db is set up is to have one row for an order in the Orders
Table, ? Rows in Cartons table joined together on Orders.orderid =
Cartons.Orderid based on the #of cartons shipped with the order. All of the
pricing and driver commissions are based on the total $'s charged from the
Orders table. So I need to update the orders table with the correct driver
information, but only if the driver is the same. Little round about I know.
"Adam Machanic" wrote:

> Again, the COUNT(*) should do exactly what you need here... can you tell m
e
> why this won't work for you:
>
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> BEGIN
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> END
> ELSE
> BEGIN
> -- do something else...
> END
>
> ... Given that, however, I should ask why you're updating your orders tab
le
> with the driver from the cartons table? Why denormalize your data like
> that?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> multiple
> these
> way
> return
> of
> message
> need
> the
> becuase
>
>|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
[vbcol=seagreen]
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of th
e
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I know
.
>
>
> "Adam Machanic" wrote:
>|||Why don't you try COUNT(DISTINCT c_driver) ?
Rather than storing the data in two places (which has data integrity
implications), have you considered creating a view that will return the data
in the way you need it for reporting?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:871ECA2C-4846-4442-83FE-53C1657A3159@.microsoft.com...
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of
the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I
know.
>|||What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
the last operation... I have a feeling you really want:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
update ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> I am writing a vb.net application that calls a stored procedure and need
some
> help.
> I am writting the procedure to check if multiple records exists and the
only
> way I can figure it out is to use @.@.RowCount, but can't get the right
result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I assume you want to update the row if it already exists and insert the row
if it doesn't exist. If so this should work...
UPDATE cartons SET
..
WHERE orderid = @.orderid
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO cartons (...) VALUES (...)
END
Hope this helps.
Paul
"John Shepherd" wrote:

> I am writing a vb.net application that calls a stored procedure and need s
ome
> help.
> I am writting the procedure to check if multiple records exists and the on
ly
> way I can figure it out is to use @.@.RowCount, but can't get the right resu
lt,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>

Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need some
help.
I am writting the procedure to check if multiple records exists and the only
way I can figure it out is to use @.@.RowCount, but can't get the right result,
please help.
What I have now is
if exists(select c_driver from cartons where orderid = @.orderid and
@.@.Rowcount = 1)
update...
I tried using the following but got an error in my vb application becuase
the Procedure was returning rows
select c_driver from cartons where orderid = @.orderid
if @.@.Rowcount = 1
update...
Is there any way to use the above query without returning rows to VB?
Is there a way to write an exists to query @.@.Rowcount?What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
the last operation... I have a feeling you really want:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
update ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> I am writing a vb.net application that calls a stored procedure and need
some
> help.
> I am writting the procedure to check if multiple records exists and the
only
> way I can figure it out is to use @.@.RowCount, but can't get the right
result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I assume you want to update the row if it already exists and insert the row
if it doesn't exist. If so this should work...
UPDATE cartons SET
...
WHERE orderid = @.orderid
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO cartons (...) VALUES (...)
END
Hope this helps.
Paul
"John Shepherd" wrote:
> I am writing a vb.net application that calls a stored procedure and need some
> help.
> I am writting the procedure to check if multiple records exists and the only
> way I can figure it out is to use @.@.RowCount, but can't get the right result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Adam Machanic" wrote:
> What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
> the last operation... I have a feeling you really want:
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> update ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > I am writing a vb.net application that calls a stored procedure and need
> some
> > help.
> >
> > I am writting the procedure to check if multiple records exists and the
> only
> > way I can figure it out is to use @.@.RowCount, but can't get the right
> result,
> > please help.
> >
> > What I have now is
> >
> > if exists(select c_driver from cartons where orderid = @.orderid and
> > @.@.Rowcount = 1)
> > update...
> >
> >
> > I tried using the following but got an error in my vb application becuase
> > the Procedure was returning rows
> >
> > select c_driver from cartons where orderid = @.orderid
> > if @.@.Rowcount = 1
> > update...
> >
> > Is there any way to use the above query without returning rows to VB?
> >
> > Is there a way to write an exists to query @.@.Rowcount?
> >
> >
> >
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Paul" wrote:
> I assume you want to update the row if it already exists and insert the row
> if it doesn't exist. If so this should work...
> UPDATE cartons SET
> ...
> WHERE orderid = @.orderid
> IF (@.@.ROWCOUNT = 0)
> BEGIN
> INSERT INTO cartons (...) VALUES (...)
> END
> Hope this helps.
> Paul
> "John Shepherd" wrote:
> > I am writing a vb.net application that calls a stored procedure and need some
> > help.
> >
> > I am writting the procedure to check if multiple records exists and the only
> > way I can figure it out is to use @.@.RowCount, but can't get the right result,
> > please help.
> >
> > What I have now is
> >
> > if exists(select c_driver from cartons where orderid = @.orderid and
> > @.@.Rowcount = 1)
> > update...
> >
> >
> > I tried using the following but got an error in my vb application becuase
> > the Procedure was returning rows
> >
> > select c_driver from cartons where orderid = @.orderid
> > if @.@.Rowcount = 1
> > update...
> >
> > Is there any way to use the above query without returning rows to VB?
> >
> > Is there a way to write an exists to query @.@.Rowcount?
> >
> >
> >|||Again, the COUNT(*) should do exactly what you need here... can you tell me
why this won't work for you:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
BEGIN
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
END
ELSE
BEGIN
-- do something else...
END
... Given that, however, I should ask why you're updating your orders table
with the driver from the cartons table? Why denormalize your data like
that?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> I tried the count() but I don't get what I need
> what I need to do is determin is if a single driver is assigned to
multiple
> cartons within an order. for Instance say driver #1 was assigned to Carton
> 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
these
> cartons are in Order #1000
> Order# Carton# Driver#
> 1000 100 1
> 1000 101 24
> 1000 102 24
> What I need to get to is this without the select (becuase doing it this
way
> returns an error in my vb.net application because the select wants to
return
> rows)
> select c_driver from Cartons where orderid = @.orderid
> if @.@.RowCount = 1
> --Only one driver exists for this order
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> if @.@.RowCount > 1
> -- Multiple Driver exists for this Order
>
> "Adam Machanic" wrote:
> > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
of
> > the last operation... I have a feeling you really want:
> >
> > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > update ...
> >
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
message
> > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > I am writing a vb.net application that calls a stored procedure and
need
> > some
> > > help.
> > >
> > > I am writting the procedure to check if multiple records exists and
the
> > only
> > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > result,
> > > please help.
> > >
> > > What I have now is
> > >
> > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > @.@.Rowcount = 1)
> > > update...
> > >
> > >
> > > I tried using the following but got an error in my vb application
becuase
> > > the Procedure was returning rows
> > >
> > > select c_driver from cartons where orderid = @.orderid
> > > if @.@.Rowcount = 1
> > > update...
> > >
> > > Is there any way to use the above query without returning rows to VB?
> > >
> > > Is there a way to write an exists to query @.@.Rowcount?
> > >
> > >
> > >
> >
> >
> >|||The problem with count() is that for my example would return 3 rows, I can
only update when all cartons in the orders have the same driver#.
I left out the DISTINCT in my example it should be:
select DISTINCT c_driver from Cartons where orderid = @.orderid
If all drivers are the same for the cartons in the Order I should have 1
row, if there a seperate drivers I should have more than 1 row
The way the db is set up is to have one row for an order in the Orders
Table, ? Rows in Cartons table joined together on Orders.orderid =Cartons.Orderid based on the #of cartons shipped with the order. All of the
pricing and driver commissions are based on the total $'s charged from the
Orders table. So I need to update the orders table with the correct driver
information, but only if the driver is the same. Little round about I know.
"Adam Machanic" wrote:
> Again, the COUNT(*) should do exactly what you need here... can you tell me
> why this won't work for you:
>
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> BEGIN
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> END
> ELSE
> BEGIN
> -- do something else...
> END
>
> ... Given that, however, I should ask why you're updating your orders table
> with the driver from the cartons table? Why denormalize your data like
> that?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> > I tried the count() but I don't get what I need
> >
> > what I need to do is determin is if a single driver is assigned to
> multiple
> > cartons within an order. for Instance say driver #1 was assigned to Carton
> > 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
> these
> > cartons are in Order #1000
> >
> > Order# Carton# Driver#
> > 1000 100 1
> > 1000 101 24
> > 1000 102 24
> >
> > What I need to get to is this without the select (becuase doing it this
> way
> > returns an error in my vb.net application because the select wants to
> return
> > rows)
> >
> > select c_driver from Cartons where orderid = @.orderid
> > if @.@.RowCount = 1
> > --Only one driver exists for this order
> > Update orders set oDriver = (select distinct c_driver from cartons
> > where orderid = @.orderid)
> >
> > if @.@.RowCount > 1
> > -- Multiple Driver exists for this Order
> >
> >
> > "Adam Machanic" wrote:
> >
> > > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
> of
> > > the last operation... I have a feeling you really want:
> > >
> > > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > > update ...
> > >
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
> message
> > > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > > I am writing a vb.net application that calls a stored procedure and
> need
> > > some
> > > > help.
> > > >
> > > > I am writting the procedure to check if multiple records exists and
> the
> > > only
> > > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > > result,
> > > > please help.
> > > >
> > > > What I have now is
> > > >
> > > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > > @.@.Rowcount = 1)
> > > > update...
> > > >
> > > >
> > > > I tried using the following but got an error in my vb application
> becuase
> > > > the Procedure was returning rows
> > > >
> > > > select c_driver from cartons where orderid = @.orderid
> > > > if @.@.Rowcount = 1
> > > > update...
> > > >
> > > > Is there any way to use the above query without returning rows to VB?
> > > >
> > > > Is there a way to write an exists to query @.@.Rowcount?
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid => Cartons.Orderid based on the #of cartons shipped with the order. All of the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I know.
>
>
> "Adam Machanic" wrote:
> > Again, the COUNT(*) should do exactly what you need here... can you tell me
> > why this won't work for you:
> >
> >
> > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > BEGIN
> > Update orders set oDriver = (select distinct c_driver from cartons
> > where orderid = @.orderid)
> > END
> > ELSE
> > BEGIN
> > -- do something else...
> > END
> >
> >
> > ... Given that, however, I should ask why you're updating your orders table
> > with the driver from the cartons table? Why denormalize your data like
> > that?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> > news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> > > I tried the count() but I don't get what I need
> > >
> > > what I need to do is determin is if a single driver is assigned to
> > multiple
> > > cartons within an order. for Instance say driver #1 was assigned to Carton
> > > 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
> > these
> > > cartons are in Order #1000
> > >
> > > Order# Carton# Driver#
> > > 1000 100 1
> > > 1000 101 24
> > > 1000 102 24
> > >
> > > What I need to get to is this without the select (becuase doing it this
> > way
> > > returns an error in my vb.net application because the select wants to
> > return
> > > rows)
> > >
> > > select c_driver from Cartons where orderid = @.orderid
> > > if @.@.RowCount = 1
> > > --Only one driver exists for this order
> > > Update orders set oDriver = (select distinct c_driver from cartons
> > > where orderid = @.orderid)
> > >
> > > if @.@.RowCount > 1
> > > -- Multiple Driver exists for this Order
> > >
> > >
> > > "Adam Machanic" wrote:
> > >
> > > > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
> > of
> > > > the last operation... I have a feeling you really want:
> > > >
> > > > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > > > update ...
> > > >
> > > >
> > > >
> > > > --
> > > > Adam Machanic
> > > > SQL Server MVP
> > > > http://www.sqljunkies.com/weblog/amachanic
> > > > --
> > > >
> > > >
> > > > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
> > message
> > > > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > > > I am writing a vb.net application that calls a stored procedure and
> > need
> > > > some
> > > > > help.
> > > > >
> > > > > I am writting the procedure to check if multiple records exists and
> > the
> > > > only
> > > > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > > > result,
> > > > > please help.
> > > > >
> > > > > What I have now is
> > > > >
> > > > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > > > @.@.Rowcount = 1)
> > > > > update...
> > > > >
> > > > >
> > > > > I tried using the following but got an error in my vb application
> > becuase
> > > > > the Procedure was returning rows
> > > > >
> > > > > select c_driver from cartons where orderid = @.orderid
> > > > > if @.@.Rowcount = 1
> > > > > update...
> > > > >
> > > > > Is there any way to use the above query without returning rows to VB?
> > > > >
> > > > > Is there a way to write an exists to query @.@.Rowcount?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Why don't you try COUNT(DISTINCT c_driver) ?
Rather than storing the data in two places (which has data integrity
implications), have you considered creating a view that will return the data
in the way you need it for reporting?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:871ECA2C-4846-4442-83FE-53C1657A3159@.microsoft.com...
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid => Cartons.Orderid based on the #of cartons shipped with the order. All of
the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I
know.
>

Help replacing where not exists in a query

I am trying to clean up an ugly query that's based on trying to find items that exist in one table but not the other.

My tables are like this:

ITEMS
itemID,
itemName,
itemDescription
etc...

ORDERITEMS
OrderLineID,
OrderID,
itemID

ORDERS
OrderID,
OrderCompleted

Currently my query looks something like this:

Select Items.* from ITEMS where not exists (select 1 from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID where ORDERITEMS.itemID=ITEMS.itemID and (ORDERS.OrderCompleted=1))

So this query is looking for ITEMS what don't have a corresponding entry in the ORDERITEMS table. As I understand it this is pretty inefficient as it is going to be executing the sub query in the Not Exists statement for each entry in the ITEMS table. Is the preferred method to do something along the lines of somehow making the sub query into a derived table and doing a left or right join?

Thanks for reading!

Ryan

Does this help?

Find Orphans Example:

An orphan record exists when there is a Contact2 record but NOT a Contact1 record.

SELECT * FROM Contact2 WHERE AccountNoNOT IN (SELECT AccountNo from Contact1)

|||

Would the NOT IN select clause be executed on every loop though? Which is less efficient *as I understand it*, than a left join?

|||

Using NOT EXISTS is the way I've always done it and the way I've always seen it done. So long as your indexes are built right this will perform well -- doing it once per ITEM is no problem as long as there's an index ORDERITEMS.itemId. And, btw, you can simply put a Select * in the subquery, you don't need to say Select top 1.

Re Steve's suggestion, I don't know for sure, but I'm not convinced it will work well, I'd like to see a showplan with your data. Translating his suggestion into your data, I think it turns into:

select *
from ITEMS
where itemId not in (select itemId -- select distinct itemId??
from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID
where ORDERS.OrderCompleted=1)

The subselect will be executed only once, not once for each row of ITEMS.

However, I am not so sure this will perform well -- and it may be dependant on sql server 2000 vs 20005 whether it works well. Among other things, is there a limit to the number of items in an IN list? Like I say, I'd like to see the query plans

|||

If the subquery referenced by the NOT IN clause doesn't reference any fields of the outer query, then it'll be executed only once. In fact, it should have the same query plan as a left join.

select *
from ITEMS
where itemId not in (select itemId -- select distinct itemId??
from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID
where ORDERS.OrderCompleted=1)

should produce a similiar query plan to:

SELECT *

FROM items i

LEFT JOIN (SELECT itemId FROM orderitems oi JOIN orders o ON oi.OrderID=o.OrderID WHERE o.OrderCompleted=1) t1 ON i.ItemID=t1.ItemID

WHERE t1.ItemID IS NULL

|||

FYI, if OrderItems.ItemId is nullable there will be a potential for getting different results in each method, seehttp://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Motley:

In fact, it should have the same query plan as a left join.

Not trying to argue, but do you know for a fact that SQL Server resolves a nested NOT IN list subquery exactly thes same way as a NOT EXISTS? Do you have a citation on this. I really don't know myself -- for a fact -- one way or the other.

|||

I didn't say that NOT IN and NOT EXISTS produce the same query plans. As you pointed out, NOT EXISTS clauses almost always uses a WHERE/JOIN clause that references a field in the outer query, which would radically change the query plan because the results for the subquery would vary in each iteration of the outer query. In any case, I was referring to NOT IN and LEFT JOIN.

With that said, you are correct. If one or more of the fields are nullable (even if no records actually contain a null value), then it can change the query plan, sometimes significantly. As I wrote that, you'll notice I even backed off from saying that it will produce the exact same query plan (Just similiar) as I wrote the two query examples. Three-valued-logic discussions are often beyond the scope of the simple questions that people usually ask here on these forums and I prefer to keep my answers simple when possible.

The article you linked brings up some very good points regarding the use of IN, EXISTS, and JOIN when dealing with one or more columns that can possibly contain NULL values. I'd have to argue a few points the author makes regarding the "expected" results (Specificially, I suspect that most beginner SQL writers that don't understand three-valued logic would indeed expect the results that the IN gave for the last results -- No records since both the parent and child had records with an ID of NULL. However, there are other test cases that would fail, the author just happened to have test data that gave that result). Still, it's a good article that explores the differences when dealing with nullable fields.

As for a citation -- (overlooking that isn't what was meant to be conveyed) I don't trust what is said, even by experts. Only the truth is important, and even "experts" are too often wrong in what they say. It is however, an excellent starting point until I learn better.

--

After all that, the worst performing queries will be ones that use subqueries that depend on value from each row in an outer query. It is very difficult for the query planner to come up with an efficient query plan to such a query. Optimizing such a query into one that has no such dependancy whether using IN, EXISTS, or JOIN will usually produce much better results, especially as the size of the data grows.

|||

Motley:

I didn't say that NOT IN and NOT EXISTS produce the same query plans

You're right, I misquoted you. My apologies.

Motley:

As for a citation -- (overlooking that isn't what was meant to be conveyed) I don't trust what is said, even by experts. Only the truth is important, and even "experts" are too often wrong in what they say. It is however, an excellent starting point until I learn better

Huh? I'd accept a citation from eg MSDN citation or SQL Server books online, wouldn't you? Could be wrong, but they did design the product.. Simply looking at query plans from individual queries is not enough support a blanket statement. I would rephrase my question to ask whether (how) you know that this NOT IN and LEFT JOIN resolve to the same thing. I don't keep up with every advance of the optimizer, maybe you do, but you made a pretty strong claim and I'm asking on what that is based ("In fact, it should have the same query plan as a left join").

Interestingly, I just did find a 2005 refrerence in which MS says that EXISTS and IN subqueries, as we have been talking about them here, are "semantically equivilent"http://msdn2.microsoft.com/en-us/library/ms188336.aspx

I also just found this 2005 reference,http://msdn2.microsoft.com/en-us/library/ms172984.aspx, for 2005 Compact Edition (didn't know there was one) which says, "The SQL Server Compact Edition query processor always rewrites the IN subquery to use JOIN"

Motley:

After all that, the worst performing queries will be ones that use subqueries that depend on value from each row in an outer query. It is very difficult for the query planner to come up with an efficient query plan to such a query. Optimizing such a query into one that has no such dependancy whether using IN, EXISTS, or JOIN will usually produce much better results, especially as the size of the data grows.

To accept that I would definately want to see a MS reference.