Adding a subquery to an existing query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Adding a subquery to an existing query

Author

Topic

live life
Starting Member

6 Posts

Posted - 2006-05-01 : 09:02:27

Hi,

I have a query that I am using to return the top 3 news articles from a news page...which works just fine. Now I am trying to create another query that would return news articles 4-8 from the database for an archive section. I have been doing some googling about subqueries and the 'NOT IN' statement and I think that is the way to go, but I am not exactly sure how to write it. The sites that I have found were not very friendly in the sense of examples.

Here is what I have so far for the first query. How would I take this query to modify it for the other query? Your help is appreciated.

SELECT top 3 view_cms_tree_joined.*, Content_News.*
FROM view_cms_tree_joined
INNER JOIN content_news ON view_cms_tree_joined.foreignkeyvalue = content_news.NewsID
WHERE classname = 'cms.news' AND NewsReleaseDate <= GetDate() AND Published = 1 AND --(##WHERE##)
ORDER BY NewsReleaseDate DESC, newstitle

...also can you recommend sql sites that are good for beginners?

thanks,
live life

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-01 : 09:23:18

"Now I am trying to create another query that would return news articles 4-8 from the database for an archive section."
Can you explain more on this ? And also please provide the table structure and same sample data with your expected result.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-01 : 09:25:20

"can you recommend sql sites that are good for beginners?"
http://www.sqlcourse.com/
http://www.w3schools.com/sql/default.asp
http://www.sql-tutorial.net/

live life
Starting Member

6 Posts

Posted - 2006-05-01 : 09:38:43

Thanks for the quick response and suggestions for sql sites.

Can you explain more on this
I have a "Pressroom" page that is displaying the top 3 most recent stories. Below that, I have an archive section that I want to display the next five stories...so basically, when a new story is added, the last story in the top three moves to the archive section and so on.

And also please provide the table structure

Table = "Content_news"
Tablerows = "NewsID, NewsTitle, NewsSummary, NewsText, NewsReleaseDate"

So in the end, I want the archive section to return the top five articles excluding the first three which in which reality will be articles 4 through 8.

Here is my latest attempt with the subquery...which did not work.

SELECT top 5 view_cms_tree_joined.*, Content_News.*
FROM view_cms_tree_joined
INNER JOIN content_news ON view_cms_tree_joined.foreignkeyvalue = content_news.NewsID
WHERE not exists (SELECT top 3 view_cms_tree_joined.*, Content_News.*
FROM view_cms_tree_joined) AND classname = 'cms.news' AND NewsReleaseDate <= GetDate() AND Published = 1 --AND (##WHERE##)
ORDER BY NewsReleaseDate DESC, newstitle

Thanks for your help and advice.

live life

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-01 : 09:46:24

Is there any Primary Key Column the query ???

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

live life
Starting Member

6 Posts

Posted - 2006-05-01 : 13:40:08

Is there any Primary Key Column the query ???

I am new to sql, so I am not sure what you mean by Primary Key Column. Maybe I need to check out those links posted earlier to understand the fundamentals of sql better.

live life

Nystix
Starting Member

4 Posts

Posted - 2006-05-01 : 13:49:51

A primary key is a column that uniquely identifies each record for the table.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-02 : 02:01:35

Post the same output of the query which you are trying??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-02 : 02:31:16

A primary key is a column (or columns) that uniquely identifies each record for the table.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-02 : 04:34:42

quote:
Originally posted by live life

Is there any Primary Key Column the query ???

I am new to sql, so I am not sure what you mean by Primary Key Column. Maybe I need to check out those links posted earlier to understand the fundamentals of sql better.

live life

Read about constraints in sql server help file for more information

Madhivanan

Failing to plan is Planning to fail

live life
Starting Member

6 Posts

Posted - 2006-05-02 : 15:03:09

Read about constraints in sql server help file for more information

Madhivanan

Thanks, I think I will do some more reading before I continue to tackle this subquery.

Everyone, thanks for the suggestions, links and explanations.

live life

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources