Searching db

  • Thread starter Thread starter Lee Atkinson
  • Start date Start date
L

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
 
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?
Click to expand...

Can you please explain in greater detail exactly what you are trying to
achieve...
 
Lee Atkinson said:
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?
Click to expand...

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.
 
Lee Atkinson said:
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?
Click to expand...
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
 
Yeah this is something like I had in mind ... Good stuff


Lee Atkinson said:
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?
Click to expand...
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
Click to expand...
 
Back
Top