Querying two tables at once

my right brain won't let me figure it out

[mcjohnson]

hi friends,

I have a database with a number of tables, two of which are ARTICLES and CATEGORIES. The similar row in both is ArticleID.

So, I want to be able to display an article from ARTICLES where Author is 1 and where in CATEGORIES, Category is 1,2 and/or 3.

When I post the article, I can easily choose the author (Fred=1, for example) and the category (news=1, press releases=2, trade shows=3)

All of the info from the article goes into ARTICLES along w/ a new ArticleID, except the category I have chosen, which goes into CATEGORIES w/ the same ArticleID.

So now I want to display those articles - assume I have chosen both news and press releases as the category, my article will have Fred(1) as an author and will have 1 and 2 in the category row in the Categories table.

Now, on the news page (and on the press release page, but let's deal w the news page first), I want to output the article.

Is this a join, a union, or can I achieve this with a simple SELECT query? I am not quite sure where to start. I've seen a number of close-but-not quite examples, so if someone can get me started on the code, I would be extremely appreciative.

Best Regards,

Pat

[syber]

THIS is a classic JOIN situation

First write a SELECT statement that joins the two tables on a common key, in this case ArticleID.

SELECT *
FROM articles JOIN categories ON articles.ArticleID = categories.ArticleID

Once you have that working you can then adjust the column list and add a WHERE clause to get exactly what you want.

SELECT articles.*, categoryname
FROM articles JOIN categories ON articles.ArticleID = categories.ArticleID
WHERE category BETWEEN 1 AND 3