February 17, 2011 at 12:42 am
Sorry, don't quite know what to search on for this.
SELECT _TOPICS.ID, _TOPICS.TITLE, _THREAD.TITLE, _CONVERSATION.POST
FROM _TOPICS
RIGHT OUTER JOIN _THREAD
ON _TOPICS.TITLE = _THREAD.TITLE
RIGHT OUTER JOIN _CONVERSATION
ON _THREAD.DOCID = _CONVERSATION.DOCID
This is a:
A(1toMany)B(1toMany)C
and the SQL above is from the Design Query in Editor... in the Management Studio.
The data are from scraping a forum. For each topic (A:1), there are a variable number of pages (B:1-N), with a variable number of posts per page (C:1-N).
I need to limit the query to use just one row from A. The result recordset should be as many rows as there are rows in the filtered _CONVERSATION. That is, where the select criteria, only on table A, is TOP 1.
February 17, 2011 at 1:16 am
if you only need data from table A, why not use a DISTINCT and only select values from table A?
SELECT DISTINCT _TOPICS.ID, _TOPICS.TITLE
FROM _TOPICS
RIGHT OUTER JOIN _THREAD
ON _TOPICS.TITLE = _THREAD.TITLE
RIGHT OUTER JOIN _CONVERSATION
ON _THREAD.DOCID = _CONVERSATION.DOCID
That should give you only a single record for each Topic
February 17, 2011 at 5:19 am
Hello Smither,
If I don't understand wrong you want top 1 post for each discussion.
If so you can use SQL ROW_NUMBER with Partition By Clause
There is a sample at the above article.
You can enumarate rows in groups in a select.
And using this as a subselect or CTE, you can apply a where criteria to select top 1 rows.
I hope that helps,
February 17, 2011 at 9:10 am
Can you try this :
SELECT _TOPICS.ID, _TOPICS.TITLE, _THREAD.TITLE, _CONVERSATION.POST
FROM (select top 1 from _TOPICS) as _TOPICS
RIGHT OUTER JOIN _THREAD
ON _TOPICS.TITLE = _THREAD.TITLE
RIGHT OUTER JOIN _CONVERSATION
ON _THREAD.DOCID = _CONVERSATION.DOCID
February 17, 2011 at 9:12 am
If you want be sure that related record exists in all tables:
SELECT _TOPICS.ID, _TOPICS.TITLE, _THREAD.TITLE, _CONVERSATION.POST
FROM (select top 1 from _TOPICS where Title in (select Title from _THREAD where DOCID in (select DOCID from _CONVERSATION))) as _TOPICS
RIGHT OUTER JOIN _THREAD
ON _TOPICS.TITLE = _THREAD.TITLE
RIGHT OUTER JOIN _CONVERSATION
ON _THREAD.DOCID = _CONVERSATION.DOCID
February 17, 2011 at 10:18 pm
Tried the above (the simpler of the two scripts) and have a Syntax Error near the Keyword 'FROM'.
I believe I will try, as the sub-select, "(SELECT TOP 1 ID, TITLE FROM _TOPICS)". I think I recall needing to state what columns you want in the SELECT phase of the query.
OK, that sort of works, but I am going to use INNER JOIN instead of RIGHT OUTER JOIN. I don't know why the Query Builder gave me ROJ.
Thank you for your reply! It is very much appreciated.
February 19, 2011 at 8:00 am
Yes, I just missed it, you have to list columns or use * for all columns as: (select top 1 * from _TOPICS
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply