Help with select statement

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"rhaazy" <rhaazy@.gmail.com> wrote in message
news:1151698511.458117.256010@.d30g2000cwa.googlegroups.com...
> 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 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||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:
> "rhaazy" <rhaazy@.gmail.com> wrote in message
> news:1151698511.458117.256010@.d30g2000cwa.googlegroups.com...
>
> 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 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --