SQL query that includes *all* table rows
-
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 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply