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.