Hi All

I'm having some problems with query that needs to obtain values from two tables which has an m:n relationship. First I'll just lay out the tables so that you can better understand my problem.

---------------------
table Articles

ArticleID PK
Heading
Intro
Text
Category
---------------------

---------------------
table ImageUsers

ArticleID PK
ImageID PK
Position
----------------------

----------------------
table Images

ImageID PK
ImageURL
----------------------

As you can see the tables represents articles which can have one or more images associtated with them.

I need to run a query that will return the following information for all entries in table "Articles":

Article.ArticleID, Article.Heading, Article.Intro, Article.Text, Article.Category, ImageURL.

ImageURL should only have a value if the article has an entry in table "ImageUsers" and only if ImageUsers.Position = 'small', if not ImageURL in the recordset should be empty, or contain a null value.

I would also like to limit the result to articles belonging to a certain category.

I've tried different variations of joins and subqueries but I just can't seem to come up with a query that will return the correct values.

All help will be appreciated

Regards

Stian

Hello,

what about ...

SELECT a.articleid, a.heading, a.intro, a.Text, a.categroy, i.imageurl
FROM articles a, imageusers iu, images i
WHERE a.articleid = iu.articleid
AND iu.imageid = i.imageid

the result: all articles that have at least one image

Hope this helps

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

I also need to return those articles that does not have any images at all. I guess I could run a separate query to do this, but I'd rather not.

Cheers

Stian

Hello again,

than you need an outer join :

SELECT a.articleid, a.heading, a.intro, a.Text, a.categroy, i.imageurl
FROM articles a, imageusers iu, images i
WHERE a.articleid = iu.articleid(+)
AND iu.imageid = i.imageid(+)

This works for Oracle ... Which database are you using ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Hi Again

Currently I'm using a MS Access database, but will be moving to mySQL in the near future

Stian