Need help with query. - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Need help with query.

Author

Topic

tmac603
Starting Member

2 Posts

Posted - 2008-03-20 : 11:40:53

Hi, I have 2 tables that I need to get data from and display it using a .net datalist. My problem is with the SQL query.
First Table as two Columns and is a list of firmware versions
Second table is a list of switches and the firmware version is associated with the ID of the firmware version.

Table 1 firware

FirmwareVersion
pkFirmwareVerID

Table 2 has associated switch types

fkFirmwareVerRecomendedID
SwitchType

What I need to do is display the firmware as DISTINCT so it only gets displayed once for each version but the trouble comes when I want to display all the associated switches under the firmware version. I am not really sure how to go about this, I can do simple queries but when it comes to joins and case statements I am still learning. Can anyone help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-20 : 11:49:14

May be you are looking for this:-

SELECT f.FirmwareVersion,LEFT(stl.switchtype,LEN(stl.switchtype)-1) AS 'Switch Types'
FROM firware f
CROSS APPY (SELECT SwitchType  + ',' as [text()]
            FROM switchtypes 
            WHERE fkFirmwareVerRecomendedID = f.pkFirmwareVerID 
            FOR XML PATH(''))stl(switchtype)

this will give you each firmware version and give a comma seperated list of switch types associated to it.

tmac603
Starting Member

2 Posts

Posted - 2008-03-20 : 12:36:36

Thanks for the reply.

I tried it in SQL 2005 and the results look like it might be what I want but when I try using it in my ASP.NET page with a datalist I get an error displaying the SwitchType ERROR: DataBinding: 'System.Data.Common.DataRecordInternal' does not contain a property with the name 'SwitchType'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources