Select one table multiple times in one Query

Question

1.00/5 (1 vote)

See more:

SQL-server-2000

SQL-Server-2005

, +

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”);

VB

I want : ( 1, "ZYX",  "RED", "#FF0000",  25, "Lighter" ,
                      "GREEN", "#00FF00", 50, "Mid"  ,
                      "BLUE", "#0000FF", 25, "Lighter", "DDDDDDDD")

Posted 23-Sep-12 10:23am

tanvir_fu

Updated 23-Sep-12 13:36pm

Mohibur Rashid

v2

Add a Solution

Comments

[no name] 23-Sep-12 19:05pm

Okay so what have you tried?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

VIPR@T · Answer 1 · 2012-09-23T20:06:00

Hi,

Please try the following query.

SQL

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

tanvir_fu · Answer 2 · 2012-10-13T12:10:00

Thanks to you.
I Solved the problem with ...........

(Field Name change but structure is OK)

SQL

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]

============================

Select one table multiple times in one Query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0