Nested Query to Extra column in a report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Nested Query to Extra column in a report

Author

Topic

cepeterson
Starting Member

3 Posts

Posted - 2007-05-31 : 10:35:27

First, I appreciate any guidance any of you can provide on this.
I have three tables, one a list of device types, one a list of devices, and one a relational table of device_devicetypes. What I'm trying to accomplish is a single SQL stored procedure that will take in a deviceID, select *all* device types, and on device types that are in the recordset of selecting deviceTypeIDs from device_deviceTypes where the DeviceID equals the parameter, add a column to the output such as "SELECTED" as a bit type. In other words, if I have 5 device types and I call this stored procedure via something like:
EXEC getAllDeviceTypesWithSelected 33 (where 5 is a device ID)
And get an output looking something like

DeviceTypeName DeviceTypeID SELECTED
DeviceType 1 1 False
DeviceType 2 2 False
DeviceType 3 3 True
DeviceType 4 4 True
DeviceType 5 5 True

Does that make sense to anybody or am I in dire need of additional (or less) caffeine this morning?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-31 : 11:06:28

Assuming that the PK of the intersection table is a composite of DeviceTypeID and DeviceID


DECLARE @DeviceID
SET     @DeviceID = 5

SELECT    dt.DeviceTypeName, 
          dt.DeviceTypeID, 
          CASE 
            WHEN dtd.DeviceTypeID IS NULL 
            THEN 'False' 
            ELSE 'True' 
          END AS Selected
FROM      dbo.DeviceTypes AS dt
LEFT JOIN dbo.Device_DeviceTypes AS dtd
ON        dtd.DeviceTypeID = dt.DeviceTypeID
  AND     dtd.DeviceID = @DeviceID

Mark

cepeterson
Starting Member

3 Posts

Posted - 2007-05-31 : 11:36:53

mwjdavidson -
I'm definitely feeling closer now, but I get:
Msg 208, Level 16, State 1, Procedure test, Line 6
Invalid object name 'dbo.DeviceType'.
Msg 208, Level 16, State 1, Procedure test, Line 6
Invalid object name 'dbo.Device_DeviceType'.

As a response to EXEC test 14

Where test is a stored procedure defined as:
PROCEDURE [Medic].[test]
@DeviceID varchar(100) = 0
AS
BEGIN

SELECT dt.DeviceTypeName,
dt.DeviceTypeID,
CASE
WHEN dtd.DeviceTypeID IS NULL
THEN 'False'
ELSE 'True'
END AS Selected
FROM dbo.DeviceType AS dt
LEFT JOIN dbo.Device_DeviceType AS dtd
ON dtd.DeviceTypeID = dt.DeviceTypeID
AND dtd.DeviceID = @DeviceID
END

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-31 : 12:11:05

Check that the names of your tables correspond with those in the query. Check also that they are owned by dbo (I was making assumptions!) I'm a bit concerned by the definition of @DeviceID as a VARCHAR(100)!

Mark

cepeterson
Starting Member

3 Posts

Posted - 2007-05-31 : 12:40:57

Wooohoooo You're a genius... Got it with:
[Medic].[test]
@DeviceID int = 0
AS
BEGIN

SELECT dt.Name,
dt.ID,
CASE
WHEN dtd.DeviceTypeID IS NULL
THEN 'False'
ELSE 'True'
END AS Selected
FROM Medic.DeviceType AS dt
LEFT JOIN Medic.Device_DeviceType AS dtd
ON dtd.DeviceTypeID = dt.ID
AND dtd.DeviceID = @DeviceID
END

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-06-01 : 08:56:13

quote:
Wooohoooo You're a genius...

I'm blushing

Glad I could help.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources