Searching db

[Lee Atkinson]

Hey guys

Lets say I want to search a table of products , easy enough .. I also want
to return the tags associated with the product which I would usually do a
join to a 'tags' table or whatever...

How to collate the list of tags per product row without using a group by or
whatever?

L

 

[Mark Rae [MVP]]

Let's say I want to search a table of products, easy enough .. I also want
to return the tags associated with the product which I would usually do a
join to a 'tags' table or whatever...

How to collate the list of tags per product row without using a group by
or
whatever?

Can you please explain in greater detail exactly what you are trying to
achieve...

 

[Mr. Arnold]

Hey guys

Lets say I want to search a table of products , easy enough .. I also want
to return the tags associated with the product which I would usually do a
join to a 'tags' table or whatever...

How to collate the list of tags per product row without using a group by or
whatever?

You would get the resultset back on a Select of ProductID. You may have to
do a Distinct.

You walk the resultset using a dataread and for each row read, you take the
ProductID on the row and Select Tags by ProductID.

You would need a second datareader within the first datareader's read loop
and read the resultset of Tags.

 

[Paul Shapiro]

Lets say I want to search a table of products , easy enough .. I also want
to return the tags associated with the product which I would usually do a
join to a 'tags' table or whatever...

How to collate the list of tags per product row without using a group by
or
whatever?

You didn't say what database and version you're using or give the relevant
table schema. All of that would help. In SQL Server, you could add a
calculated field to the Product table, referencing a user-defined function
that concatenates the multiple 'tags' rows into a single string. For
example, here's a routine that returns a comma-separated list of the groups
an actor belongs to:

CREATE FUNCTION [dbo].[fnActorGroupList]
(
@ActorID int --Actor whose group list will be returned
)
RETURNS varchar(1024)
AS
-- =============================================
-- Description: Return comma-separated list of an actor's groups
-- Example: Select dbo.fnActorGroupList(1916) As TestList
-- =============================================
BEGIN
DECLARE @GroupList varchar(1024) --Output

--Initialize
Set @GroupList = ''
-- Add the T-SQL statements to compute the return value here
Select @GroupList = @GroupList + Coalesce(D.groupName,'') + ', '
From
(
Select Distinct ltrim(rtrim(G.group_assign)) As groupName
From dbo.tbl_actor_group AG
Join dbo.tbl_groups G ON G.group_id=AG.group_id
Where AG.actor_id = @ActorID
) As D

--Remove trailing comma
If Len(@GroupList) >= 1
Set @GroupList = substring(@GroupList, 1, Len(@GroupList)-1)

-- Return the result of the function
RETURN @GroupList
END

 

[Lee Atkinson]

Yeah this is something like I had in mind ... Good stuff

Lets say I want to search a table of products , easy enough .. I also want
to return the tags associated with the product which I would usually do a
join to a 'tags' table or whatever...

How to collate the list of tags per product row without using a group by
or
whatever?

You didn't say what database and version you're using or give the relevant
table schema. All of that would help. In SQL Server, you could add a
calculated field to the Product table, referencing a user-defined function
that concatenates the multiple 'tags' rows into a single string. For
example, here's a routine that returns a comma-separated list of the groups
an actor belongs to:

CREATE FUNCTION [dbo].[fnActorGroupList]
(
@ActorID int --Actor whose group list will be returned
)
RETURNS varchar(1024)
AS
-- =============================================
-- Description: Return comma-separated list of an actor's groups
-- Example: Select dbo.fnActorGroupList(1916) As TestList
-- =============================================
BEGIN
DECLARE @GroupList varchar(1024) --Output

--Initialize
Set @GroupList = ''
-- Add the T-SQL statements to compute the return value here
Select @GroupList = @GroupList + Coalesce(D.groupName,'') + ', '
From
(
Select Distinct ltrim(rtrim(G.group_assign)) As groupName
From dbo.tbl_actor_group AG
Join dbo.tbl_groups G ON G.group_id=AG.group_id
Where AG.actor_id = @ActorID
) As D

--Remove trailing comma
If Len(@GroupList) >= 1
Set @GroupList = substring(@GroupList, 1, Len(@GroupList)-1)

-- Return the result of the function
RETURN @GroupList
END