SQL query that includes *all* table rows
-
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.WordFROM 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 WordThis 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 http://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 -
Like this?
SELECT DISTINCT
Thread.URL,
W.Word
FROM PThread Thread
JOIN PPostWord PostWord
ON Thread.ID = PostWord.ThreadID
AND Thread.URL = 'www.foo.com'
JOIN PWord W
ON W.ID = PostWord.WordID
JOIN WordList List
ON W.Word = List.SearchWord
/Kenneth
-
You can use a CROSS JOIN on the table for which you want all rows returned.
-
Kenneth,
Thanks for the quick reply. I won't be able to try out your suggestion until tonight. I'll let you know how it goes.
But, one additional question: In your suggestion you hard-code in the value 'www.foo.com'. But I don't know ahead of time what the contents of the rows of the WordList table are. How can I specify, in the query, that value without hardcoding it?
Thanks again.
Robert
-
Lee,
Thanks for the response. I think I didn't explain the original problem as well as I could have. I don't want to return all the rows on the table, I just want the contents of all the rows of one of the columns of the table to be specified in the query. Or, maybe I don't understand well enough how to use a cross join?
Robert
-
Hehe, well in your example you requested the value 'www.foo.com', so that's what you need to ask for.
You could provide the URL through a variable instead, I assume that you do know which URL you're looking for sometime before actually running the qurey..? (if not I'm lost to what the actual problem is)
DECLARE @URL varchar(100)
SET @URL = 'www.foo.com'
SELECT DISTINCT
Thread.URL,
W.Word
FROM PThread Thread
JOIN PPostWord PostWord
ON Thread.ID = PostWord.ThreadID
AND Thread.URL = @URL
JOIN PWord W
ON W.ID = PostWord.WordID
JOIN WordList List
ON W.Word = List.SearchWord
/Kenneth
-
Kenneth,
Thanks again. OK, you caught me. I left out a couple of details, which I thought weren't relevant, but actually are.
. Here's the missing information:Here's the beginning of the stored procedure:
CREATE PROCEDURE getURLs(@CommaDelimitedListOfWords Param)
At the top of the SP, I parse Param into a number of strings. This number is only knowable at run-time, not compile-time. Each string is then added to a row in the temporary WordList table, which I create in the SP.
Does this explain things better?
Thanks again.
Robert
-
okay assuming your temp table is called #wl and contains all the words you want
and you have a table PThread that has ID and URL fields
and you have a table PPostWord that has ThreadID and Word then...
decalre @countofwords int
select @countofwords=count(*) from #wl
select distinct
URL,
Word
From
(
select Pthread.URL URL,PPostWord.Word WORD,count(distinct #wl.word)
From Pthread
Join PPostWord on PPostWord.ThreadId=Pthread.ID
join #wl on #wl.word=PPostWord.word
group by Pthread.URL,PPostWord.Word
having count(distinct #wl.word)=@countofwords
) AS Derived
I haven't had chance to create the scenario and test it but it should give you an idea
BTW as a URL is by definition unique why do you need an ID?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply