SQL Server Performance Forum – Threads Archive

A little bit hard select statement

Hello,
I Have 3 tables one for items (itemid and item name), this table has 1 to many relation ship to other 2 tables: Table one (TRLessons) Table 2 (TRKeystages)
I need to make a select statement from the 3 tables to retrive info of all items which may include the word ‘box’ Here is the tables structure
Tables:
CREATE TABLE [dbo].[TRItems] (
[ItemId] [int] IDENTITY (1, 1) NOT NULL ,
[IName] [varchar] (50) NULL , ) ON [PRIMARY]
GO CREATE TABLE [dbo].[TRILessons] (
[ItemId] [int] NOT NULL ,
[LessonId] [char] (1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TRIKeystages] (
[ItemId] [int] NOT NULL ,
[KeystageId] [tinyint] NOT NULL
) ON [PRIMARY]
GO Insert Into TRItems(ItemId, IName) Values (1, ‘Food Box’)
Insert Into TRItems(ItemId, IName) Values (2, ‘Doll Box’)
Insert Into TRItems(ItemId, IName) Values (3, ‘Pencils Package’)
Insert Into TRILessons(ItemId, LessonId) Values (1, 1)
Insert Into TRILessons(ItemId, LessonId) Values (1, 4)
Insert Into TRILessons(ItemId, LessonId) Values (2, 3)
Insert Into TRIKeystages (ItemId, KeystageId) Values (1, 2)
Insert Into TRIKeystages (ItemId, KeystageId) Values (1, 3)
Insert Into TRIKeystages (ItemId, KeystageId) Values (2, 4)
Insert Into TRIKeystages (ItemId, KeystageId) Values (2, 5)
Requirments:
I need to construct a select st. that retrieves the 2 items which include the word ‘box’
as:
Itemid || ItemName || ALLLessons|| AllKeystages||
1 || FoodBox || 1,4 || 2,3 ||
2 || Doll Box || 3 || 4,5 ||

My main select statemnet which does not give the required output:
Select I.ItemId, I.IName, L.ItemId, L.LessonId, K.ItemId, K.KeystageId
From TRItems As I inner Join TRILessons As L
on I.ItemId = L.ItemId
Inner Join TRIKeystages As K
on I.ItemId = K.ItemId
Where I.IName LIKE ‘%’ + @ItemName + ‘%’
I also tried to select the values of each record of the the ‘box’ items in variables and concatenate the lessons and the keystages and put record by record in a table variable but the select st. did not work Can anybody help?
Thanks a lot Rasha zaki
Web Developer
Cairo, Egypt

I think that functions are your answer here…
create function sf_get_lessons(
@p_itemidint
)
returns varchar(500)
as
begin declare @returnvarchar(500) [email protected] = isnull( @return + ‘,’, ” ) + convert( varchar(10), lessonid )
fromdbo.trilessons
whereitemid = @p_itemid return @return
end go
create function sf_get_stages(
@p_itemidint
)
returns varchar(500)
as
begin declare @returnvarchar(500) [email protected] = isnull( @return + ‘,’, ” ) + convert( varchar(10), Keystageid )
fromdbo.trikeystages
whereitemid = @p_itemid return @return
end go
SelectI.ItemId,
I.IName,
AllLessons = dbo.sf_get_lessons( i.itemid ),
AllStages = dbo.sf_get_stages( i.itemid )
From TRItems I
Where I.IName LIKE ‘%box%’ gives 1Food Box1,42,3
2Doll Box34,5
Cheers
Twan

Hi Twan, Thanks a lot [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />], your solution worked perfectly but I just wonder if this can be acomplished in one select statment, I mean with no functions. Thanks a lot. Rasha zaki Web Developer Cairo, Egypt

Hi Rasha, no you cannot do this with one select statement as far as I know… Cheers
Twan

]]>