tricky sql (parent and child in one result)

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

Transact-SQL (2000)

tricky sql (parent and child in one result)

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2003-10-28 : 17:56:25

I have a dilema that I run into time to time and I don't have a good solution. For example, I need to return a result set from table 'Fields' for all records where category = 'Cat1'. In that same result set, I want to return the FieldState info (many to one), preferably in a comma delimited string. So the results might be like this


FieldID		Category	FieldName	State
1		Cat1		Name1		WA,OR,ID
2		Cat1		Name2		WA,OR

I have read this great article http://www.sqlteam.com/item.asp?ItemID=2368 but it falls short in the scenario where multiple main records are returned (in this case 2 records). The only way I have sucessfully done this is through multiple queries. (first get the main result set and then get the child (state) result set. When populating the output, loop through the child set to get/format the data as above.) I'm sure there is a better way.


CREATE TABLE Fields (
	[FieldID] [int] NOT NULL ,
	[Category] [varchar] (20) NOT NULL,
	[FieldName] [varchar] (20) NOT NULL ,
)
go

CREATE TABLE FieldStates (
	[FieldID] [int] NOT NULL ,
	[State] [varchar] (2) NOT NULL 
)
go

INSERT INTO Fields VALUES (1,'Cat1','Name1')
go
INSERT INTO Fields VALUES (2,'Cat1','Name2')
go
INSERT INTO Fields VALUES (3,'Cat2','Name3')
go
INSERT INTO FieldStates VALUES (1,'WA')
go
INSERT INTO FieldStates VALUES (1,'OR')
go
INSERT INTO FieldStates VALUES (1,'ID')
go
INSERT INTO FieldStates VALUES (2,'WA')
go
INSERT INTO FieldStates VALUES (2,'OR')
go
INSERT INTO FieldStates VALUES (3,'ID')
go

Nic

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-10-28 : 18:38:25

How about this? It uses a UDF to make it work, but maybe someone can convert it to be a single statement. I could not make it work as a single statement.


CREATE TABLE Fields (
	[FieldID] [int] NOT NULL ,
	[Category] [varchar] (20) NOT NULL,
	[FieldName] [varchar] (20) NOT NULL ,
)
go

CREATE TABLE FieldStates (
	[FieldID] [int] NOT NULL ,
	[State] [varchar] (2) NOT NULL 
)
go

INSERT INTO Fields VALUES (1,'Cat1','Name1')
go
INSERT INTO Fields VALUES (2,'Cat1','Name2')
go
INSERT INTO Fields VALUES (3,'Cat2','Name3')
go
INSERT INTO FieldStates VALUES (1,'WA')
go
INSERT INTO FieldStates VALUES (1,'OR')
go
INSERT INTO FieldStates VALUES (1,'ID')
go
INSERT INTO FieldStates VALUES (2,'WA')
go
INSERT INTO FieldStates VALUES (2,'OR')
go
INSERT INTO FieldStates VALUES (3,'ID')
go
CREATE FUNCTION [dbo].[tempudf_StateList] (@FieldID INT)  
RETURNS VARCHAR(100) AS  
BEGIN 
	DECLARE @StateList VARCHAR(100)
	
	SELECT @StateList = COALESCE(@StateList + ', ', '') + State
	FROM Fields f
	INNER JOIN FieldStates fs ON fs.FieldID = f.FieldID
	WHERE f.FieldID = @FieldID
	GROUP BY f.FieldID, f.Category, f.FieldName, fs.State
	ORDER BY fs.State

	RETURN @StateList

END
Go


SELECT f.Category, f.FieldName, dbo.tempudf_StateList(f.FieldID)
FROM Fields f
INNER JOIN FieldStates fs ON fs.FieldID = f.FieldID
GROUP BY f.FieldID, f.Category, f.FieldName
GO


DROP FUNCTION  [dbo].[tempudf_StateList]
DROP TABLE FieldStates
DROP TABLE Fields

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources