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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

User1713851783 posted

there are more Id-s in CommentsTable, which are not joined

Try to give me more informations on CommentsTable definition.

Answer 5

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";

Answer 6

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";

Answer 7

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?

Answer 8

User1713851783 posted

f CommentsTable is empty, it would be an empty query result

Use LEFT JOIN instead of INNER JOIN

Answer 9

User-352524747 posted

Thanks for your help.