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] (
           PK[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].[tblEmployee]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblEmployee]
GO

CREATE TABLE [dbo].[tblEmployee] (
    PK[EmpID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
    [EmployeeID] [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].[tblEmployeeAsset]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblEmployeeAsset]
GO

CREATE TABLE [dbo].[tblEmployeeAsset] (
    PK[EmpID] [int] NOT NULL ,
    PK[AssetID] [int] NOT NULL ,
    [DateCreated] [datetime] NULL ,
    [LastModified] [datetime] NULL ,
    [Deleted] [bit] NULL
) ON [PRIMARY]
GO
-----------------------------------------------------------------
tblAsset
AssetID       AssetName
1                    RyanPC
------------------------
tblEMployee
EmpID            EmployeeID
1                     Ryan
----------------------------
tblemployeeasset
EmpID           AssetID
1                       1

I want a select statement that gives me this result...
Employee             Asset
Ryan                   RyanPC

SELECT E.EmployeeID, A.AssetName
FROM tblAsset AS A
JOIN tblEmployeeAsset AS T
  ON A.AssetID = T.AssetID
JOIN tblEmployee AS E
  ON T.EmpID = E.EmpID ;

This works exactly the way I want in the querly analyzer thank you.

However, I am calling this select statement from within some C# code
via a dataadapter, when I run the query I get an error, any idea why?

SqlDataAdapter da = new SqlDataAdapter("select e.EmployeeID,e.EmpID,
a.AssetName, a.AssetId    from tblAsset a    join tblEmployeeAsset t  on
a.assetID = t.AssetID  join tblEmployee e on t.EmpID = E.EmpID", str);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dataGrid1.DataSource = ds;
The connection exists, and every other thing works, but why not this?
If this is beyond your scope i will promptly post in a C# forum...

David Portas wrote:
Show quoteHide quote

SQL question
Incorrect information from a "View"
Function results
More Efficient SQL Statement - Select Count(1)
Passing a parameter as list in a Stored procedure