an SQL join question which I can not figure out

Suppose I have three tables like this:


table weblogEntries{
id
headline
mainContent
dateCreated
}

table keywords {
id
keyword
}

table whatKeywordsApplyToWhichEntries {
id
entryId
keywordId
}


Any time a record in whatPropertiesApplyToWhichEntries has an entryId
and a keywordId, then that entry has had that keyword assigned to it.

If I wanted to get an entry, along with all keywords that have been
applied to it, how would I do so? If I don't know how many keywords
have been applied to the weblog entry, what's the syntax of the join?

re: an SQL join question which I can not figure out

On 9 Jun 2005 13:45:19 -0700, lkrubner@geocities.com wrote:
[color=blue]
>Suppose I have three tables like this:
>
>table weblogEntries{
>
>table keywords {
>
>table whatKeywordsApplyToWhichEntries {
>
>Any time a record in whatPropertiesApplyToWhichEntries has an entryId
>and a keywordId, then that entry has had that keyword assigned to it.
>
>If I wanted to get an entry,[/color]

So that's one row.
[color=blue]
> along with all keywords that have been applied to it,[/color]

That's potentially multiple rows.

So either

* two queries (one for the entry, another for the keywords), or

* end up with repeats in the data returned for the entries fields, from a join
with a the entry-keyword mapping table, one row per keyword.
[color=blue]
>if I don't know how many keywords
>have been applied to the weblog entry, what's the syntax of the join?[/color]

If you were thinking you can get a variable number of columns out of this, one
per keyword in a single row then forget that idea; that's not how SQL works.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool