12,691,959 members (28,964 online)
Rate this:
See more: , +
I have Three Tables. I want to make a Select Query which will produce the result.

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
Updated 23-Sep-12 14:36pm
v2

Okay so what have you tried?

Rate this:

## Solution 1

Hi,

```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```

Thanks,
Viprat
Maciej Los 14-Oct-12 11:51am

Good work, my 5!
Rate this:

## 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]
```

============================
v2
Maciej Los 14-Oct-12 11:52am

Top Experts
Last 24hrsThis month
 OriginalGriff 255 CPallini 155 Dave Kreskowiak 145 KarstenK 120 ppolymorphe 105
 OriginalGriff 2,888 Peter Leow 1,910 Mika Wendelius 1,743 Jochen Arndt 1,234 ppolymorphe 1,211