locked
How to join another table in the query. RRS feed

  • 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 result

    Use 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 joined

    Try 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 result

    Use 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