Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I have Three Tables. I want to make a Select Query which will produce the result.
Please Help me ...
 
Color
1. Id
2. Name
3. Code
 
Values( 1, “RED”, “#FF0000”);
Values( 2, “GREEN”, “#00FF00”);
Values( 3, “BLUE”, “#0000FF”);
 
Percentage
1. Id
2. Percentage
3. Description
 
Values( 1, 0, ”Zero” );
Values( 2, 25, ”Lighter” );
Values( 3, 50, ”Mid” );
Values( 4, 75, ”Higher” );
Values( 5, 100, ”Full” );
 
SampleTable
1. Id
2. Name
3. Color_Id1
4. Percentage_Id1
5. Color_Id2
6. Percentage_Id2
7. Color_Id3
8. Percentage_Id3
9. Description
Values( 1, ”ZYX”, 1, 2, 2, 3, 3, 2, ”DDDDDDDDDDD”);
 
I want : ( 1, ”ZYX”,  “RED”, “#FF0000”,  25, ”Lighter” ,
                      “GREEN”, “#00FF00”, 50, ”Mid”  ,
                      “BLUE”, “#0000FF”, 25, ”Lighter”, “DDDDDDDD”)
Posted 23-Sep-12 10:23am
Edited 23-Sep-12 13:36pm
v2
Comments
Wes Aday at 23-Sep-12 19:05pm
   
Okay so what have you tried?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
Please try the following query.
 
SELECT     SampleTable.ID, SampleTable.Name, Color.Name AS Expr1, Color.Code, Percentage.Percentage, Percentage.Description, Color_1.Name AS Expr2, 
                      Color_1.Code AS Expr3, Percentage_1.Percentage AS Expr4, Percentage_1.Description AS Expr5, Color_2.Name AS Expr6, Color_2.Code AS Expr7, 
                      Percentage_2.Percentage AS Expr8, Percentage_2.Description AS Expr9
FROM         SampleTable INNER JOIN
                      Color ON SampleTable.Color_Id1 = Color.ID INNER JOIN
                      Percentage ON SampleTable.Percentage_Id1 = Percentage.ID INNER JOIN
                      Color AS Color_1 ON SampleTable.Color_Id2 = Color_1.ID INNER JOIN
                      Percentage AS Percentage_1 ON SampleTable.Percentage_Id2 = Percentage_1.ID INNER JOIN
                      Color AS Color_2 ON SampleTable.Color_Id3 = Color_2.ID INNER JOIN
                      Percentage AS Percentage_2 ON SampleTable.Percentage_Id3 = Percentage_2.ID
 
I hope it might be help you.
 
Thanks,
Viprat
  Permalink  
Comments
Maciej Los at 14-Oct-12 11:51am
   
Good work, my 5!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Thanks to you.
I Solved the problem with ...........
 
(Field Name change but structure is OK)
Select 
 [SampleTable].[Id] AS [Id],
 [SampleTable].[Name] AS [Name],
 [SampleTable].[Color_Id1] AS [Color Id1],
 [Color].[Id] AS [Color_Id],
 [Color].[Color_Name] AS [Color_Color Name],
 [Color].[Description] AS [Color_Description],
 [SampleTable].[Percentage_Id1] AS [Percentage Id1],
 [Percentage].[Id] AS [Percentage_Id],
 [Percentage].[Percentage] AS [Percentage_Percentage],
 [Percentage].[Description] AS [Percentage_Description],
 [SampleTable].[Color_Id2] AS [Color Id2],
 [Color4].[Id] AS [Color4_Id],
 [Color4].[Color_Name] AS [Color4_Color Name],
 [Color4].[Description] AS [Color4_Description],
 [SampleTable].[Percentage_Id2] AS [Percentage Id2],
 [Percentage5].[Id] AS [Percentage5_Id],
 [Percentage5].[Percentage] AS [Percentage5_Percentage],
 [Percentage5].[Description] AS [Percentage5_Description],
 [SampleTable].[Color_Id3] AS [Color Id3],
 [Color6].[Id] AS [Color6_Id],
 [Color6].[Color_Name] AS [Color6_Color Name],
 [Color6].[Description] AS [Color6_Description],
 [SampleTable].[Percentage_Id3] AS [Percentage Id3],
 [Percentage7].[Id] AS [Percentage7_Id],
 [Percentage7].[Percentage] AS [Percentage7_Percentage],
 [Percentage7].[Description] AS [Percentage7_Description],
 [SampleTable].[Details] AS [Details]
 
from  [SampleTable] 
 INNER JOIN [Color] AS [Color4] ON [SampleTable].[Color_Id2] = [Color4].[Id]
 INNER JOIN [Percentage] AS [Percentage5] ON [SampleTable].[Percentage_Id2] = [Percentage5].[Id]
 INNER JOIN [Color] AS [Color6] ON [SampleTable].[Color_Id3] = [Color6].[Id]
 INNER JOIN [Percentage] AS [Percentage7] ON [SampleTable].[Percentage_Id3] = [Percentage7].[Id]
 , [Color], [Percentage]
where 
 [Color].[Id] = [SampleTable].[Color_Id1] AND
 [Percentage].[Id] = [SampleTable].[Percentage_Id1] 
============================
  Permalink  
v2
Comments
Maciej Los at 14-Oct-12 11:52am
   
If VIPR@T's answer was helpfull, please rate it and mark as 'solved'.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 468
1 Sergey Alexandrovich Kryukov 305
2 PhilLenoir 115
3 RajeeshMenoth 110
4 Peter Leow 100
0 Sergey Alexandrovich Kryukov 6,465
1 OriginalGriff 5,926
2 CPallini 2,473
3 Richard MacCutchan 1,687
4 Abhinav S 1,540


Advertise | Privacy | Mobile
Web02 | 2.8.140821.2 | Last Updated 14 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100