CODEWITHSUNDEEP
sqlinner-join
Daniel_Kamel
Daniel_Kamel

Reputation: 619

Inner Join on the same table

I have 2 queries, the first:

Select Distinct App_Form_Name,Control_Name
From Application_Form_Controls_Management
where Control_Type = 'ASPxGridView'
Order By App_Form_Name, Control_Name

which will return(for example):

App_Form_Name | Control_Name
Form1         | ControlName1
Form1         | ControlName2

The 2nd:

Select Distinct App_Form_Name, Right_ID, App_Form_Function_ID
From Application_Form_Controls_Management
where Control_Type = 'ASPxGridView'
Order By App_Form_Name,Right_ID,App_Form_Function_ID

which will return(for example):

App_Form_Name | Right_ID | App_Form_Function_ID
Form1         | 1        | 1
Form1         | 1        | 2
Form1         | 1        | 3
Form1         | 2        | 1
Form1         | 2        | 1
Form1         | 2        | 1

What I want is the Inner Join query that will return me the following result:

App_Form_Name | Control_Name | Right_ID | App_Form_Function_ID
Form1         | ControlName1 | 1        | 1
Form1         | ControlName1 | 1        | 2
Form1         | ControlName1 | 1        | 3
Form1         | ControlName1 | 2        | 1
Form1         | ControlName1 | 2        | 2
Form1         | ControlName1 | 2        | 3
Form1         | ControlName2 | 1        | 1
Form1         | ControlName2 | 1        | 2
Form1         | ControlName2 | 1        | 3
Form1         | ControlName2 | 1        | 1
Form1         | ControlName2 | 1        | 2
Form1         | ControlName2 | 1        | 3

Thanks.

Upvotes: 0

Views: 83

Answers (1)

Eray Balkanli
Eray Balkanli

Reputation: 7990

You need to use your queries as sub queries like below:

Select a.App_Form_Name,a.Control_Name, b.Right_ID, b.App_Form_Function_ID
From
   (
    Select Distinct App_Form_Name,Control_Name From Application_Form_Controls_Management where Control_Type='ASPxGridView' 
   ) a
Inner join (
              Select Distinct App_Form_Name,Right_ID,App_Form_Function_ID From 
              Application_Form_Controls_Management where Control_Type='ASPxGridView'
           ) b
on a.App_Form_Name = b.App_Form_Name

If you are using SQL Server, you can also benefit from cte structure like:

;with cte as (
   Select Distinct App_Form_Name,Control_Name From 
   Application_Form_Controls_Management where Control_Type='ASPxGridView' 
),
cte2 as (
   Select Distinct App_Form_Name,Right_ID,App_Form_Function_ID From 
                   Application_Form_Controls_Management where Control_Type='ASPxGridView'
)
select cte.App_Form_Name,cte.Control_Name, cte2.Right_ID, cte2.App_Form_Function_ID
from cte
inner join cte2 on cte.App_Form_Name = cte2.App_Form_Name

Upvotes: 1

Related Questions