CODEWITHSUNDEEP
asp.netsql
DaveZ
DaveZ

Reputation: 3

How to Join a query to another query

I have two queries, and I want to join the first query with the second in SQL, here are my queries,

FIRST QUERY

OleDbCommand Query1 = new OleDbCommand();
Query.CommandText = "SELECT [Mastertable].ID " +
"FROM [Mastertable] INNER JOIN [relationship] ON [Mastertable].ID = [relationship].SW1_ID " +
"WHERE ([Mastertable].[PN]= @PN AND [Mastertable].[Ver]=@Ver) " +
"GROUP BY [Mastertable].[ID]";

DataSet ResultSet = RunQuery(Query1);

The above query basic return the ID where PN = PN input and Ver = Version input.( I put the result in a Dataset

I want to join the ID column from query1 to Mastertable. ID again

the 2nd query,

OleDbCommand Query2 = new OleDbCommand();
Query2.CommandText = "SELECT [Mastertable].PN, [Mastertable].[Ver], [relationship].[Category], [Mastertable].[Component_Name] " +
"FROM (Query1 INNER JOIN [relationship] ON Query1.[ID] = [relationship].SID) INNER JOIN " +
"[Mastertable] ON [relationship].SW2_ID = [Mastertable].[ID]";

DataSet ResultSet2 = RunQuery(Query2);

but this code doesn't work, I searched on the web alot and tried a few, but they didn't help. I was thinking to use the ResultSet1.Tables[0].TableName and ResultSet1.Tables[0].Columns[0].Name, but that didn't work =(

Please help

Upvotes: 0

Views: 61

Answers (3)

StuartLC
StuartLC

Reputation: 107407

You can join Query1 to the second (outer) Query2 as a derived table as follows:

SELECT [SW Mastertable].SW_PN, [SW Mastertable].[SW_Ver], 
       [SW relationship].[SW2_Category], [SW Mastertable].[Component_Name]
FROM 
  (SELECT [Mastertable].ID
    FROM [Mastertable] 
      INNER JOIN [relationship] ON [Mastertable].ID = [relationship].SW1_ID
    WHERE ([Mastertable].[PN]= @PN AND [Mastertable].[Ver]=@Ver)
    GROUP BY [Mastertable].[ID]
  ) Query1
  INNER JOIN [SW relationship] ON Query1.[ID] = [SW relationship].SW1_ID) 
  INNER JOIN [SW Mastertable] ON [SW relationship].SW2_ID = [SW Mastertable].[ID];

If you are using sql-server you can also do this with a CTE:

WITH Query1 AS
(
  SELECT [Mastertable].ID
    FROM [Mastertable] 
        INNER JOIN [relationship] ON [Mastertable].ID = [relationship].SW1_ID
    WHERE ([Mastertable].[PN]= @PN AND [Mastertable].[Ver]=@Ver)
    GROUP BY [Mastertable].[ID]
)
SELECT [SW Mastertable].SW_PN, [SW Mastertable].[SW_Ver], 
       [SW relationship].[SW2_Category], [SW Mastertable].[Component_Name]
FROM 
  Query1
    INNER JOIN [SW relationship] ON Query1.[ID] = [SW relationship].SW1_ID) 
    INNER JOIN [SW Mastertable] ON [SW relationship].SW2_ID = [SW Mastertable].[ID];

Upvotes: 1

Vikram Jain
Vikram Jain

Reputation: 5588

OleDbCommand Query2 = new OleDbCommand();
Query2.CommandText = "SELECT [SW Mastertable].SW_PN, [SW Mastertable].[SW_Ver], [SW relationship].[SW2_Category]," + 
" [SW Mastertable].[Component_Name] " +
" FROM " +
"(SELECT [Mastertable].ID as ID " +
"FROM [Mastertable] INNER JOIN [relationship] ON [Mastertable].ID = [relationship].SW1_ID " +
"WHERE ([Mastertable].[PN]= @PN AND [Mastertable].[Ver]=@Ver) " +
"GROUP BY [Mastertable].[ID]) as Query1 " +
" INNER JOIN [SW relationship] ON Query1.[ID] = [SW relationship].SW1_ID INNER JOIN " +
"[SW Mastertable] ON [SW relationship].SW2_ID = [SW Mastertable].[ID]";

DataSet ResultSet2 = RunQuery(Query2);

Upvotes: 0

samar
samar

Reputation: 5211

Not very sure if this will work but still you can try it out. Also please fix the syntax errors if any.

Also I am assuming that you have the Query1 object available (i.e. not null or not out of scope) while trying to create Query2 object.

OleDbCommand Query2 = new OleDbCommand();
Query2.CommandText = "SELECT [SW Mastertable].SW_PN, [SW Mastertable].[SW_Ver], [SW relationship].[SW2_Category], [SW Mastertable].[Component_Name], [SW relationship].SW2_ID " +
"FROM (("+ Query1.CommandText +")TAB1 INNER JOIN [SW relationship] ON TAB1.[ID] = [SW relationship].SW1_ID) TAB2 INNER JOIN " +
"[SW Mastertable] ON [TAB2].SW2_ID = [SW Mastertable].[ID]";

DataSet ResultSet2 = RunQuery(Query2);

Hope this helps.

Upvotes: 0

Related Questions