August 13, 2008 at 4:46 am
Hi,
i'm trying to retrieve data from 3 tables. They are
Table1: Station
Sample Data : StationCode StationDescription
1 A
2 B
3 c
Table2 : StationCategoryLink
SampleData : StationCode CategoryCode
1 AB, CD
2 GH
3 AB, GH
Table3: CategoryMaster
SampleData : CategoryCode Description
AB XXX
CD YYY
GH ZZZ
Expected Output:
StationCode StationDescription Description
1 A XXX, YYY
2 B ZZZ
3 C XXX, ZZZ
Can you please help me in eriting this query, or can you refer me a useful link?
Thanks&Regards,
Anil.
August 13, 2008 at 11:48 pm
You have to split up the values in table 2. If you put this into a temp table, you can join this with table 3.
August 13, 2008 at 11:56 pm
Or, with the sample you are giving, you can just use a nested REPLACE function in your query...
REPLACE(
REPLACE(
Table2.CategoryCode
, Table3.CategoryCode
, Table3.Descripton)
, Table3.CategoryCode
, Table3.Descripton)
But this is only possible if the code are not somewhere in the description.
August 15, 2008 at 4:22 pm
You have a many-to-many relationship between stations and categories but have not modeled the intersection table correctly. Instead of
Table2 : StationCategoryLink
SampleData : StationCode CategoryCode
1 AB, CD
2 GH
3 AB, GH
the correct form would be
Table2 : StationCategoryLink
SampleData : StationCode CategoryCode
1 AB
1 CD
2 GH
3 AB
3 GH
The StationCode column is a FK reference to the Station table, the CategoryCode column is a FK reference to the Category table, both together form the PK of the StationCategoryLink table. Now it is easy to list all categories associated with a station or all stations associated with a particular category.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
August 16, 2008 at 4:03 am
Plz try the same
Declare @RowCount int, @ActualCount int, @StationCode Int
Declare @List Varchar(50), @SplitOn Varchar(1)
Select @RowCount = Count(1) From StationCategoryLink
Set @ActualCount =1
While @RowCount>=@ActualCount
Begin
Select @StationCode = StationCode,@List =CategoryCode From StationCategoryLink Where StationCode=@ActualCount
--Set @List = 'AB,CD'
Set @SplitOn =','
Declare @RtnValue Table(Id Int Identity(1,1),StationCode Int, CategoryCode Varchar(100))
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (StationCode,CategoryCode)
Select StationCode=@StationCode,
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (StationCode,CategoryCode)
Select StationCode=@StationCode,Value = ltrim(rtrim(@List))
--Select * From @RtnValue
Set @ActualCount = @ActualCount + 1
End
Select * From @RtnValue B
Inner Join CategoryMaster C On C.CategoryCode=B.CategoryCode
--Create Table #Result(StationCode int, StationDescription Varchar(100), Description Varchar(100))
--Insert Into #Result Select StationCode,StationDescription,NULL From Station
--Delete from #Result
Select @RowCount = Count(1) From @RtnValue
Set @ActualCount =1
While @RowCount>=@ActualCount
Begin
Select @StationCode = StationCode From @RtnValue Where id=@ActualCount
Update A Set A.Description = COALESCE(A.Description + ', ', '') +Cast(B.Description As varchar(50))
From #Result A
Inner Join (Select B.StationCode,C.Description From @RtnValue B
Inner Join CategoryMaster C On C.CategoryCode=B.CategoryCode) B On A.StationCode = B.StationCode
Where A.StationCode=@StationCode
Delete From @RtnValue Where id=@ActualCount
Set @ActualCount = @ActualCount + 1
End
Select * From #Result
But I strongly suggest to change the DB structure.
August 17, 2008 at 7:28 am
Hi,
I appreciate the work by Jaya and i have also tried to look into it and here is another query that you can also try. - Good luck,
SELECT T1.StationDescription as SD_T1,T2.StationCode,T2.StationDescription, T2.StationDescription CDiscription
into #Temp1
FROM Table1 T1
Inner join Table2 T2 on T1.StationCode = T2.StationCode
Begin
Declare @Tot_Rows int
,@Counter int
Select identity(int,1,1) id,* into #Temp2 from Table3
Set @Tot_Rows = @@Rowcount
Set @Counter=1
WHILE @Counter<=@Tot_Rows
Begin
Update t1 Set CDiscription=replace(CDiscription,Categorycode,CDescription)
from #Temp1 t1 with (nolock)
inner join #Temp2 T2 on ID=@Counter
Set @Counter=@Counter+1
End
Drop Table #Temp2
Select * From #Temp1
End
Note:
but i ll also suggest you it is good for you to change the structure of your table as suggested above by Tomm and Jaya.
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
August 17, 2008 at 12:32 pm
i would apply some normaliastion to table 2 as suggested first as you'll find yourself ending up with some extra tables which will change the query anyway
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 19, 2008 at 11:47 pm
This table is not normalized please work on the database design. Once done, rest all will be a piece of cake.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply