Answered by:
How to join another table in the query.

Question
-
User-352524747 posted
Join another table in the query below:
var query = "SELECT * FROM (SELECT Id, Name, DateD AS [Date], ContentD AS Content, 'd' AS TableContent FROM Daily UNION ALL SELECT Id, Quote, DateW AS [Date], ContentW AS Content, 'w' AS TableContent FROM Weekly UNION ALL SELECT Id, Quote, DateM AS [Date], ContentM AS Content, 'm' AS TableContent FROM Monthly) AS Tables WHERE Quote LIKE @0 OR [Date] LIKE @0 OR Content LIKE @0 ORDER BY [Date] DESC";
var querycomments = "SELECT Comment, CommentDate FROM CommentsTable";
How to join CommentsTable with Tables?
Friday, May 17, 2013 8:04 PM
Answers
-
User1713851783 posted
If the foreign key in CommentTable is the PgId field, you could try something like:
var query = @"SELECT * FROM (SELECT Id, Name, DateD AS [Date], ContentD AS Content, 'd' AS TableContent FROM Daily UNION ALL SELECT Id, Quote, DateW AS [Date], ContentW AS Content, 'w' AS TableContent FROM Weekly UNION ALL SELECT Id, Quote, DateM AS [Date], ContentM AS Content, 'm' AS TableContent FROM Monthly) AS Tables INNER JOIN CommentsTable ON (CAST(Tables.Id AS nvarchar(10)) + Tables.TableContent) = (CAST(CommentsTable.PgId AS nvarchar(10)) + SUBSTRING(CommentsTable.PgName, 1, 1)) WHERE Quote LIKE @0 OR [Date] LIKE @0 OR Content LIKE @0 ORDER BY [Date] DESC";
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Saturday, May 18, 2013 10:10 AM -
User1713851783 posted
f CommentsTable is empty, it would be an empty query resultUse LEFT JOIN instead of INNER JOIN
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Saturday, May 18, 2013 11:28 AM
All replies
-
User354987146 posted
SELECT * FROM Table x LEFT OUTER JOIN (SELECT Col as something, SomeID FROM Table2 GROUP BY SomeID) y ON x.SomeID = y.SomeID
Please try this, sometimes this will help you to describe your query
Friday, May 17, 2013 11:20 PM -
User1713851783 posted
How to join CommentsTable with Tables?
If TableId is the foreign key in CommentsTable, your query could be
var query = @"SELECT * FROM (SELECT Id, Name, DateD AS [Date], ContentD AS Content, 'd' AS TableContent FROM Daily UNION ALL SELECT Id, Quote, DateW AS [Date], ContentW AS Content, 'w' AS TableContent FROM Weekly UNION ALL SELECT Id, Quote, DateM AS [Date], ContentM AS Content, 'm' AS TableContent FROM Monthly) AS Tables LEFT JOIN CommentsTable ON Tables.Id = CommentsTable.TableId WHERE Quote LIKE @0 OR [Date] LIKE @0 OR Content LIKE @0 ORDER BY [Date] DESC";
I have used LEFT JOIN because maybe there isn't a comment for any row in Tables; otherwise, replace it with INNER JOIN.
Saturday, May 18, 2013 3:27 AM -
User-352524747 posted
INNER and LEFT JOIN does not produce the required search results, because there are more Id-s in CommentsTable, which are not joined.
Saturday, May 18, 2013 4:48 AM -
User1713851783 posted
there are more Id-s in CommentsTable, which are not joinedTry to give me more informations on CommentsTable definition.
Saturday, May 18, 2013 6:02 AM -
User-352524747 posted
This is CommentTable
Id NameId UserId Comment CommentDate PgId PgName 1 1 1 comment on page one-daily 5/17/2013 9:27:58 PM 1 daily 2 1 1 This is monthly 5/17/2013 9:31:16 PM 1 monthly 3 1 1 monthly 2 5/17/2013 9:45:19 PM 5 monthly 4 3 1 monthly ndx 1 5/17/2013 9:45:35 PM 3 monthly 5 1 1 daily 2 5/17/2013 9:46:07 PM 2 daily 6 4 1 comment SPXSPXSPXSPXSPX 5/17/2013 9:51:28 PM 5 weekly Below one of the tables in UNION statement, two others have same column.
Id Name DateM CoM 1 EURUSD 4/30/2013 12:00:00 AM EurUsd Monthly 2 INDU 4/30/2013 12:06:22 AM Indu Monthly 3 NDX 4/30/2013 12:00:00 AM Ndx Monthly 4 SPX 4/30/2013 12:00:00 AM Spx Monthly This is the query i use to perform a search in three tables.
var query = "SELECT * FROM (SELECT Id, Name, DateD AS [Date], ContentD AS Content, 'd' AS TableContent FROM Daily UNION ALL SELECT Id, Name, DateW AS [Date], ContentW AS Content, 'w' AS TableContent FROM Weekly UNION ALL SELECT Id, Name, DateM AS [Date], ContentM AS Content, 'm' AS TableContent FROM Monthly) AS Tables WHERE Name LIKE @0 OR [Date] LIKE @0 OR Content LIKE @0 ORDER BY [Date] DESC";
Saturday, May 18, 2013 6:29 AM -
User1713851783 posted
If the foreign key in CommentTable is the PgId field, you could try something like:
var query = @"SELECT * FROM (SELECT Id, Name, DateD AS [Date], ContentD AS Content, 'd' AS TableContent FROM Daily UNION ALL SELECT Id, Quote, DateW AS [Date], ContentW AS Content, 'w' AS TableContent FROM Weekly UNION ALL SELECT Id, Quote, DateM AS [Date], ContentM AS Content, 'm' AS TableContent FROM Monthly) AS Tables INNER JOIN CommentsTable ON (CAST(Tables.Id AS nvarchar(10)) + Tables.TableContent) = (CAST(CommentsTable.PgId AS nvarchar(10)) + SUBSTRING(CommentsTable.PgName, 1, 1)) WHERE Quote LIKE @0 OR [Date] LIKE @0 OR Content LIKE @0 ORDER BY [Date] DESC";
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Saturday, May 18, 2013 10:10 AM -
User-352524747 posted
This expression is dependent on the number of rows in the CommentsTable. So if CommentsTable is empty, it would be an empty query result. Would have been better if i use two queries and join them in a foreach loop?Saturday, May 18, 2013 11:18 AM -
User1713851783 posted
f CommentsTable is empty, it would be an empty query resultUse LEFT JOIN instead of INNER JOIN
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Saturday, May 18, 2013 11:28 AM -
User-352524747 posted
Thanks for your help.
Saturday, May 18, 2013 5:56 PM