Database Table Relations Issue

Situation: I got news. NEws are posted in multiple categories.

Issue: How to store relations between news and categories? Should I create three tables (news, categories, news_categories)? Is this the best method possible?

Let's see examples: I want to query database for latest news, so I need to: 1. query for the news using join to query for news categories 2. in the loop, query to get categories name.

So, I have 10 news (10 queries) X amount of categories = total queries. Not too much?

Update

Here's my example. There are three following tables.

-- news --  
ID  
Title

-- categories --  
ID  
Name

-- news_categories --  
ID    
NewsID  
CategoryID

No matter what query I use, it returns all projects and projects categories names correctly, however... projects are displayed few times, according to amount of categories. Maybe I'm too tired or something, but I really can't se开发者_C百科e any solution.

Update 2

Here's my query:

SELECT N.Title, C.Name
  FROM x_news_categories AS NC
    INNER JOIN x_news AS N
      ON N.ID = NC.NewsID
    INNER JOIN x_categories AS C
      ON C.ID = NC.CategoryID

and here's its result:

Title   Name
Test    PHP
Test2   MySQL
Test2   CSS

Anybody has any suggestions how to solve that issue?

Regards, M.

Yes three tables is the correct implemenation with foreign keys between news and newscategory and category and newscategory.

You're query could be something like this to return all the News Titles for a specific category name:

SELECT N.Title
  FROM news_categories AS NC
    INNER JOIN news AS N
      ON N.ID = NC.NewsID
    INNER JOIN Categories AS C
      ON C.ID = NC.CategoryID
  WHERE C.Name = @Category

You could simplify it if you already knew the CategoryID, which you probably would from a dropdown or something, then the Join to the Category table would be unnecessary and you write something like:

SELECT N.Title
  FROM news_categories AS NC
    INNER JOIN news AS N
      ON N.ID = NC.NewsID
  WHERE NC.CategoryID = @CategoryID

To select All News items with their categories in a comma separated list you would need a User Defined Function. Here the SQL code needed to help with the logic, but the actual implementation is up to you:

CREATE FUNCTION fnCategoryList
(
    @NewsID INT
)
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @CategoryList VARCHAR(1000)
    SET @CategoryList = ''

    SELECT @CategoryList = COALESCE(@CategoryList + ',','') + C.[Name]
        FROM news_categories AS NC
            INNER JOIN categories AS C
                ON NC.CategoryID = C.ID
        WHERE NC.NewsID = @NewsID

    RETURN @CategoryList
END

Using the above UDF your query would look like this:

SELECT Title, fnCategoryList(ID) AS Categories
  FROM news

Depending on the number of records in your news table, this query will not perform very well. You should have some form of WHERE clause on nearly every query you write.