SQL Problem

[addisss]

HI folks,

I have created a set of 6 tables, the three i am having a problem with are made up of a many-to-many relationship.

Table 1 = Article
Table 2 = Subject
Table 3 = Article/Subject

What i am trying to do is to output a list of all article names (Article) which are associated with a one specific subject held in (Article/Subject).

e.g.

A S
1, 2, 3 1
2, 4 2


Subject 1 thus has article 1, 2 and 3
Subject 2 has article 2, 4

I can manage to list the Article ID's from the (Article/Subject) table, but unable to join the Article table to produce the names(Article.ArticleNames).

Is there an SQL query which is going to help me get pass this point? Please advise

[Jimbob]

I don't want to sound patronising, but....

is there a reason for having many to many relationships, because if not you would be best redesigning your tables so you have one to many relationships instead.

[Ianpbaker]

Hi addisss

You haven't actually got a many to many relationship but two one to many relation's. By having the the third table you have got rid of the many problem. for one article in the article table you can have many in the joint table and for one subject in the the subject table you can have many in the joint table. To return the required result set use the following SQL.


SELECT sub.idcolumn, art.column2, art.column3 FROM article as art subject as sub, subject/article as subart
WHERE sub.idcolumn = subart.subjectcode AND subart.articlecode = art.articlecode AND sub.idcolumn = subject value


This should sort out your problem

Hope it helps
Ian