CODEWITHSUNDEEP
sqlsql-serversql-server-2012coldfusion
TRose
TRose

Reputation: 1738

SQL - how to query two tables and return the results as one?

Long story short, I have this website where "Parent" level comments are stored in a separate table than their replies, known as "Child" comments.

To produce a list of all Recent Comments, I've been doing something like this:

SELECT
TOP 10 
 PC.ParentCommentText,
 PC.ParentCommentID,
 PC.ParentCommentTimeStamp
FROM
  ParentComments AS PC

We'll call that Query1.

Query2 is the same:

SELECT
TOP 10 
 CC.ChildCommentText,
 CC.ChildCommentID,
 CC.ChildCommentTimeStamp
FROM
  ChildComments AS CC

Then I use a Union and query the queries so I can loop through all the results at once and display a mixed list of most recent.

SELECT * FROM Query1
UNION 
SELECT * FROM Query2
ORDER BY ParentCommentTimeStamp DESC

My problem starts with the first two queries - I can only return a certain number from either table to start. So if some thread runs away and becomes a big discussion, all the slots for "Child" comments are filled, and you might still see "Parent" comments that are hours or days old, despite them not being anywhere near Recent.

It's been awhile since I've done anything requiring deep knowledge of SQL. I know there's a way to select Parent and Child comments as one, and just grab __ number of the results, despite which table they originated from.

Help?

Upvotes: 1

Views: 3491

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use union all to combine the query results and order by later to get the top n rows.

SELECT TOP 10 WITH TIES *
FROM (SELECT ParentCommentText,ParentCommentID,ParentCommentTimeStamp as comment_timestamp
      FROM ParentComments 
      UNION ALL
      SELECT ChildCommentText,ChildCommentID,ChildCommentTimeStamp
      FROM ChildComments
     ) T
ORDER BY comment_timestamp DESC

Upvotes: 4

Related Questions