Reputation: 3
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
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
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
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