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 11:23am
Edited 23-Sep-12 14: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)



Advertise | Privacy | Mobile
Web01 | 2.8.150327.1 | Last Updated 14 Oct 2012
Copyright © CodeProject, 1999-2015
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