Click here to Skip to main content
12,070,351 members (75,596 online)
Rate this:
 
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 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 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 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
Top Experts
Last 24hrsThis month


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