Help with a Query

Here is some drastically stripped down DDL for a Help Desk system I
wrote. I only left the relevant columns, and didn't script any of the
relationships, etc.
CREATE TABLE [HelpDesk_Issue] ([Id] [int])
GO
INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
GO
CREATE TABLE [HelpDesk_IssueHistory] (
[Id] [int],
[IssueId] [int],
[UserIdEnteredBy] [int],
[DateEntered] [datetime]
GO
INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
[DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
GO
INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
[DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
GO
INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
[DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
GO
HelpDesk_Issue is a table containing Help Desk issue entries, and
HelpDesk_IssueHistory is a table containing modification history
records for the Help Desk issues.
I want to write a query to retrieve values for LastUpdated, and
LastUpdatedBy.
LastUpdated is pretty easy. I might simply be brainfarting on not
knowing how to do a HAVING properly, but the only way I can retrieve
LastUpdatedBy is:
SELECT
LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
Where IssueId = I.[Id]),
LastUpdatedBy =
(
Select
UserIdEnteredBy
From
HelpDesk_IssueHistory
Where
Id =
(
Select
MAX(H.Id)
From
HelpDesk_IssueHistory H
Where
IssueId = I.[Id]
)
)
FROM
HelpDesk_Issue I
My result set should be:
Date Entered UserIdEnteredBy
2004-11-05 15:25:18.120 3
This query works, but is unacceptably slow, and there's got to be a
cleaner way of doing it.
Thank you!Hi
Maybe something like:
SELECT I.id, I.DateEntered, I.UserIdEnteredBy AS LastUpdatedBy
FROM HelpDesk_Issue I
JOIN (Select Id, MAX(DateEntered) AS LatestDateEntered From
HelpDesk_IssueHistory GROUP BY Id ) L ON I.id = L.id and I.DateEntered =
L.LatestDateEntered
Assuming that DateEntered is unique!
John
<george.durzi@.gmail.com> wrote in message
news:1122230790.079548.152910@.g44g2000cwa.googlegroups.com...
> Here is some drastically stripped down DDL for a Help Desk system I
> wrote. I only left the relevant columns, and didn't script any of the
> relationships, etc.
> CREATE TABLE [HelpDesk_Issue] ([Id] [int])
> GO
> INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
> GO
> CREATE TABLE [HelpDesk_IssueHistory] (
> [Id] [int],
> [IssueId] [int],
> [UserIdEnteredBy] [int],
> [DateEntered] [datetime]
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
> GO
> HelpDesk_Issue is a table containing Help Desk issue entries, and
> HelpDesk_IssueHistory is a table containing modification history
> records for the Help Desk issues.
> I want to write a query to retrieve values for LastUpdated, and
> LastUpdatedBy.
> LastUpdated is pretty easy. I might simply be brainfarting on not
> knowing how to do a HAVING properly, but the only way I can retrieve
> LastUpdatedBy is:
> SELECT
> LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
> Where IssueId = I.[Id]),
> LastUpdatedBy =
> (
> Select
> UserIdEnteredBy
> From
> HelpDesk_IssueHistory
> Where
> Id =
> (
> Select
> MAX(H.Id)
> From
> HelpDesk_IssueHistory H
> Where
> IssueId = I.[Id]
> )
> )
> FROM
> HelpDesk_Issue I
> My result set should be:
> Date Entered UserIdEnteredBy
> 2004-11-05 15:25:18.120 3
> This query works, but is unacceptably slow, and there's got to be a
> cleaner way of doing it.
> Thank you!
>|||george.durzi@.gmail.com wrote:
> Here is some drastically stripped down DDL for a Help Desk system I
> wrote. I only left the relevant columns, and didn't script any of the
> relationships, etc.
> CREATE TABLE [HelpDesk_Issue] ([Id] [int])
> GO
> INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
> GO
> CREATE TABLE [HelpDesk_IssueHistory] (
> [Id] [int],
> [IssueId] [int],
> [UserIdEnteredBy] [int],
> [DateEntered] [datetime]
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
> GO
> HelpDesk_Issue is a table containing Help Desk issue entries, and
> HelpDesk_IssueHistory is a table containing modification history
> records for the Help Desk issues.
> I want to write a query to retrieve values for LastUpdated, and
> LastUpdatedBy.
> LastUpdated is pretty easy. I might simply be brainfarting on not
> knowing how to do a HAVING properly, but the only way I can retrieve
> LastUpdatedBy is:
> SELECT
> LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
> Where IssueId = I.[Id]),
> LastUpdatedBy =
> (
> Select
> UserIdEnteredBy
> From
> HelpDesk_IssueHistory
> Where
> Id =
> (
> Select
> MAX(H.Id)
> From
> HelpDesk_IssueHistory H
> Where
> IssueId = I.[Id]
> )
> )
> FROM
> HelpDesk_Issue I
> My result set should be:
> Date Entered UserIdEnteredBy
> 2004-11-05 15:25:18.120 3
> This query works, but is unacceptably slow, and there's got to be a
> cleaner way of doing it.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Hmm..., Your query seems to be "saying" get the row w/ the latest date
and then get the user ID associated w/ the highest IssueHistory ID
number, which doesn't make much sense. From your limited DDL the
HelpDesk_IssueHistory ID column seems to be unnecessary (how is it an
attribute of the entity HelpDesk_IssueHistory?); therefore, that's why
your query doesn't make much sense to me.
If you just want to find the users who entered the last history item on
each issue try:
SELECT DateEntered, UserIDEnteredBy
FROM HelpDesk_IssueHistory As H
WHERE DateEntered = (SELECT MAX(DateEntered)
FROM HelpDesk_IssueHistory
WHERE IssueID = H.IssueID)
If you wanted the last entry of a specific issue use the above query as
the SQL statement in a stored procedure w/ a parameter of @.issue_id INT
and change the subquery's select clause to:
WHERE IssueID = @.issue_id
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQuPwQIechKqOuFEgEQKoIgCgyRRhsqCibrj+
zwfoQQYrlPTLkWcAoJmJ
50uIn26qiIk4AFnDVinfq+CN
=OEk/
--END PGP SIGNATURE--|||Thank you both for taking the time to reply on a Sunday.
"MGFoster" wrote:

> george.durzi@.gmail.com wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Hmm..., Your query seems to be "saying" get the row w/ the latest date
> and then get the user ID associated w/ the highest IssueHistory ID
> number, which doesn't make much sense. From your limited DDL the
> HelpDesk_IssueHistory ID column seems to be unnecessary (how is it an
> attribute of the entity HelpDesk_IssueHistory?); therefore, that's why
> your query doesn't make much sense to me.
> If you just want to find the users who entered the last history item on
> each issue try:
> SELECT DateEntered, UserIDEnteredBy
> FROM HelpDesk_IssueHistory As H
> WHERE DateEntered = (SELECT MAX(DateEntered)
> FROM HelpDesk_IssueHistory
> WHERE IssueID = H.IssueID)
> If you wanted the last entry of a specific issue use the above query as
> the SQL statement in a stored procedure w/ a parameter of @.issue_id INT
> and change the subquery's select clause to:
> WHERE IssueID = @.issue_id
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQuPwQIechKqOuFEgEQKoIgCgyRRhsqCibrj+
zwfoQQYrlPTLkWcAoJmJ
> 50uIn26qiIk4AFnDVinfq+CN
> =OEk/
> --END PGP SIGNATURE--
>|||Hey guys, sorry, still having a little trouble with this.
How would you tackle this if you couldn't guarantee that DateEntered was
unique. That's why I included the Id column in HelpDesk_IssueHistory. It's a
n
identity column, I forgot to note that on my DDL.
The query I wrote fetches the id of the latest history record, then uses
that to fetch the User who entered the records. However, it's unacceptably
slow.
Thank you
"george.durzi@.gmail.com" wrote:

> Here is some drastically stripped down DDL for a Help Desk system I
> wrote. I only left the relevant columns, and didn't script any of the
> relationships, etc.
> CREATE TABLE [HelpDesk_Issue] ([Id] [int])
> GO
> INSERT INTO HelpDesk_Issue ([Id]) VALUES (1)
> GO
> CREATE TABLE [HelpDesk_IssueHistory] (
> [Id] [int],
> [IssueId] [int],
> [UserIdEnteredBy] [int],
> [DateEntered] [datetime]
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (1, 1, 1, '2004-10-27 14:41:58.980')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (2, 1, 1, '2004-10-28 16:25:38.103')
> GO
> INSERT INTO HelpDesk_IssueHistory ([Id], [IssueId], [UserIdEnteredBy],
> [DateEntered]) VALUES (3, 1, 3, '2004-11-05 15:25:18.120')
> GO
> HelpDesk_Issue is a table containing Help Desk issue entries, and
> HelpDesk_IssueHistory is a table containing modification history
> records for the Help Desk issues.
> I want to write a query to retrieve values for LastUpdated, and
> LastUpdatedBy.
> LastUpdated is pretty easy. I might simply be brainfarting on not
> knowing how to do a HAVING properly, but the only way I can retrieve
> LastUpdatedBy is:
> SELECT
> LastUpdated =(Select MAX(H.DateEntered) From HelpDesk_IssueHistory H
> Where IssueId = I.[Id]),
> LastUpdatedBy =
> (
> Select
> UserIdEnteredBy
> From
> HelpDesk_IssueHistory
> Where
> Id =
> (
> Select
> MAX(H.Id)
> From
> HelpDesk_IssueHistory H
> Where
> IssueId = I.[Id]
> )
> )
> FROM
> HelpDesk_Issue I
> My result set should be:
> Date Entered UserIdEnteredBy
> 2004-11-05 15:25:18.120 3
> This query works, but is unacceptably slow, and there's got to be a
> cleaner way of doing it.
> Thank you!
>|||The reason DateEntered isn't unique is that even though I am inserting two
history records right after each other in separate db calls, I'm still
getting consecutive history records with the same datetime value.
I'm using GETDATE() within the insert sp. This isn't happening all the time,
only on about 40 of my 8000 records, but thus causing the queries you
recommended to break.
Perhaps I can handle for the server being too fast, by not using getdate,
and instead handling it on the presentation layer, and adding a time tick to
the next insert, in order to guarantee uniqueness
"George Durzi" wrote:
> Hey guys, sorry, still having a little trouble with this.
> How would you tackle this if you couldn't guarantee that DateEntered was
> unique. That's why I included the Id column in HelpDesk_IssueHistory. It's
an
> identity column, I forgot to note that on my DDL.
> The query I wrote fetches the id of the latest history record, then uses
> that to fetch the User who entered the records. However, it's unacceptably
> slow.
> Thank you
> "george.durzi@.gmail.com" wrote:
>|||Hi
Datatime is accurate one three-hundredth of a second, therefore it is
possible to get duplicates under a heavy load, although your identity
will be unique and you can (probably) use that instead and ignore the
datetime column.
e.g.
SELECT I.IssueId, I.DateEntered AS LastUpdatedBy, I.UserIdEnteredBy AS
LastUpdatedBy
FROM HelpDesk_Issue I
JOIN (Select IssueId, MAX(Id) AS LatestId From
HelpDesk_IssueHistory GROUP BY IssueId ) L ON I.IssueId = L.IssueId and
I.Id =
L.LatestId
OR
SELECT H.IssueId, H.DateEntered, H.UserIDEnteredBy
FROM HelpDesk_IssueHistory As H
WHERE H.Id = (SELECT MAX(Id)
FROM HelpDesk_IssueHistory S
WHERE S.IssueID = H.IssueID)
John|||Thanks again John, works perfectly
"John Bell" wrote:

> Hi
> Datatime is accurate one three-hundredth of a second, therefore it is
> possible to get duplicates under a heavy load, although your identity
> will be unique and you can (probably) use that instead and ignore the
> datetime column.
> e.g.
> SELECT I.IssueId, I.DateEntered AS LastUpdatedBy, I.UserIdEnteredBy AS
> LastUpdatedBy
> FROM HelpDesk_Issue I
> JOIN (Select IssueId, MAX(Id) AS LatestId From
> HelpDesk_IssueHistory GROUP BY IssueId ) L ON I.IssueId = L.IssueId and
> I.Id =
> L.LatestId
> OR
> SELECT H.IssueId, H.DateEntered, H.UserIDEnteredBy
> FROM HelpDesk_IssueHistory As H
> WHERE H.Id = (SELECT MAX(Id)
> FROM HelpDesk_IssueHistory S
> WHERE S.IssueID = H.IssueID)
> John
>

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 a query

I have a table with 4 relevant fields (blank lines added for clarity).
State, City, Name, Primary_Contact
IL, Springfield, Bill, n
IL, Springfield, Frank, n
IL, Springfield, Larry, n

IL, Bloomington, Steve, n
IL, Bloomington, Chris, y

IL, Chicago, Betty, n
IL, Chicago, Linda, n
IL, Chicago, Sue, n

I need a query to return the state and cities that don't have a
Primary_Contact='y'
So the results would be:
IL, Springfield
IL, Chicago

That's it. Any help is greatly appreciated.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

SELECT State, City
FROM table_name
WHERE Primary_Contact<>'y'
GROUP BY State, City

--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUBYA4echKqOuFEgEQLJTACgsOAgruNlQX254w4Abe/ychTn9IAAn11t
O+xXdFBxIeubcPHE0uh6fyoi
=YNNo
--END PGP SIGNATURE--

foneguy2 wrote:

Quote:

Originally Posted by

I have a table with 4 relevant fields (blank lines added for clarity).
State, City, Name, Primary_Contact
IL, Springfield, Bill, n
IL, Springfield, Frank, n
IL, Springfield, Larry, n
>
IL, Bloomington, Steve, n
IL, Bloomington, Chris, y
>
IL, Chicago, Betty, n
IL, Chicago, Linda, n
IL, Chicago, Sue, n
>
I need a query to return the state and cities that don't have a
Primary_Contact='y'
So the results would be:
IL, Springfield
IL, Chicago
>
That's it. Any help is greatly appreciated.
>

|||On 25 Oct 2006 15:58:36 -0700, foneguy2 wrote:

Quote:

Originally Posted by

>I have a table with 4 relevant fields (blank lines added for clarity).
>State, City, Name, Primary_Contact
>IL, Springfield, Bill, n
>IL, Springfield, Frank, n
>IL, Springfield, Larry, n
>
>IL, Bloomington, Steve, n
>IL, Bloomington, Chris, y
>
>IL, Chicago, Betty, n
>IL, Chicago, Linda, n
>IL, Chicago, Sue, n
>
>I need a query to return the state and cities that don't have a
>Primary_Contact='y'
>So the results would be:
>IL, Springfield
>IL, Chicago
>
>That's it. Any help is greatly appreciated.

Hi foneguy2,

The solution posted by MGFoster won't work, unfortunately. It will give
you all State/City combo's that have at least one Primary_Contact='n'.

Here's the "straightforward" solution:

SELECT DISTINCT a.State, a.City
FROM YourTable AS a
WHERE NOT EXISTS
(SELECT *
FROM YourTable AS b
WHERE b.State = a.State
AND b.City = a.City
AND b.Primary_Contact = 'n');

And here's a more clever (but harder to graps) solution that might run a
bit faster:

SELECT State, City
FROM YourTable
GROUP BY State, City
HAVING MIN(Primary_Contact) = 'n';

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply).

--
Hugo Kornelis, SQL Server MVP|||>I have a table with 4 relevant fields [sic] .. <<

Suggestions:
1) learn why a column is nothing like a field
2) Use a numeric code instead of a fake Boolean flag. That way the
MIN() will give you a contact in every location. I would bet you spend
time updating the flags.

CREATE TABLE Contacts
(state_code CHAR(2) NOT NULL,
city_name CHAR(25) NOT NULL,
contact_name CHAR(25) NOT NULL,
contact_priority INTEGER NOT NULL
CHECK (contact_priority 0),
PRIMARY KEY (state_code, city_name, contact_name, contact_priority));

This will show one contact in every city

CREATE VIEW PrimaryContacts (state_code, city_name, contact_name)
AS
SELECT C1.state_code, C1.city_name, C1.contact_name
FROM Contacts AS C1
WHERE contact_priority
= (SELECT MIN (C2.contact_priority)
FROM Contacts AS C2
WHERE C1.state_code = C2.state_code
AND C1.city_name = C2.city_name);