SQL query that includes *all* table rows

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

Transact-SQL (2000)

SQL query that includes *all* table rows

Author

Topic

rmyhill
Starting Member

4 Posts

Posted - 2005-08-10 : 08:27:43

Hi,

I'm trying to figure out how to include *all* rows of a particular table (in this case the "WordList" table) in a MS-SQL (Server 2000) query. For example, I start with the following query:

SELECT DISTINCT Thread.URL, W.Word
FROM PThread Thread, PPostWord PostWord, PWord W, WordList List
WHERE
W.Word = List.SearchWord AND
W.ID = PostWord.WordID AND
Thread.ID = PostWord.ThreadID
GO

Let's say the WordList table has just two rows and one column, named SearchWord. One row has "the" in the SearchWord column. The second row has "and" in the SearchWord column.

The result of the above query is something like:

URL Word

www.foo.com the
www.foo.com and
www.bar.com the
www.boo.com and
www.aho.com and

This is close to what I want, but not exactly. I want to modify the above query so that, in this example, only the rows with www.foo.com in the URL column are returned. That is, I want the SELECT statement to only return rows that contain *all* the words in the WordList table for a particular URL.

Help?

Thanks.

Robert

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-10 : 08:42:16

use left outer join or right outer joins and use conditions in joins rather than in where clause

Kapil Arya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 09:05:39

Try this


Select Distinct URL From yourtable T
WHERE exists (SELECT URL FROM yourtable  WHERE word='the' and URL =T.URL )
and exists (SELECT URL FROM yourtable  WHERE word ='and' and URL =T.URL )

Madhivanan

Failing to plan is Planning to fail

rmyhill
Starting Member

4 Posts

Posted - 2005-08-10 : 09:56:50

Madhivanan,

Thanks for your quick reply. I left out something, though, which is important.

The table in my example where 'the' and 'and' come from, is a temporary table that I create at the beginning of the stored procedure and populate in the stored procedure. So, how would your write your suggested SP code without hard-coding in 'the' and 'and', but rather getting those values from my temporary table. The temporary table contains one row for each word, so in this example, the temporary table would contain 2 rows, one holding 'the' and one holding 'and'.

Thanks again.

Robert

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 10:07:23

Not sure whether this is efficient way

Select Distinct URL From yourtable T
WHERE exists 
(SELECT URL FROM yourtable WHERE word=(select top 1 word from temptable order by word) and URL =T.URL )
and exists 
(SELECT URL FROM yourtable  WHERE word =(select top 1 word from temptable order by word desc) and URL =T.URL )

Madhivanan

Failing to plan is Planning to fail

rmyhill
Starting Member

4 Posts

Posted - 2005-08-10 : 10:24:22

Hi,

Thanks again for your reply.

A couple of things: The number of rows in the temptable is variable. So how could your suggestion be modified to account for a variable number of words (known only at runtime in the SP) in the temptable?

Also, newbie question: How would I write the code where you say "(select top 1 word from temptable order by word)"

Thanks again.

Robert

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 10:28:17

>>The number of rows in the temptable is variable

If it is, then you have to use as many exists as there are records in the temp table in which this will be difficult than using dynamic SQL

>>How would I write the code where you say "(select top 1 word from temptable order by word)"

The code itself is a valid query

Madhivanan

Failing to plan is Planning to fail

rmyhill
Starting Member

4 Posts

Posted - 2005-08-10 : 10:42:39

>>If it is, then you have to use as many exists as there are records in the temp table in which >>this will be difficult than using dynamic SQL

Do you have any suggestions as to how I can try to write these exists in this way, to iterate through all the records in the temptable? Is it possible using a loop of some kind? Any other suggestions?

Thanks again.

Robert

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources