1:n relationship changes to m:n

[DianaW]

Dear All,

I have the following problem and I would be happy if somebody could help me with that: So far I had two tables. One contained customer orders, the other a description of each article. The connection between both tables was through a unique article name, which was the primary key of the ArticleDescription table. So far this worked.

But now it becomes more complicated, as there is no longer a n:1-relationship between the two tables Orders and ArticleDescription as each article now can have different specifications, which however to no relate to the items in the ArticleDescription table. That is, I have to include a table between those two tables.

Old situation:

tbOrders - Old
Order# // Article# // Date // Amount etc.
1 // A // 09/18/07 // 5
2 // B // 09/19/07 // 3
3 // A // 09/20/07 // 2

tbDescriptionArticle
Article# // Description etc.
A // Text1
B // Text2

An SQL query to combine information of both could for example look like the following...

Select tbOrders.Order#, tbOrders.Article#, tbDescriptionArticle.Description
From tbOrders Inner Join tbDescriptions On tbOrders.Article# = tbDescriptionArticle.Article#;

...in order to get this output:

Order# // Article# // Description
1 // A // Text1
2 // B // Text2
3 // A // Text1

This changes now to:

tbOrders - New
Order# // Article#New // Date // Amount etc.
1 // A1 // 09/18/07 // 5
2 // B1 // 09/19/07 // 3
3 // A2 // 09/20/07 // 2

...and I have additionally a table, which includes the relationships

tbRelationships
Article#New // Article#
A1 // A
A2 // A
B1 // B

...while tbDescriptionArticles remains the same.

How does the SQL query change in order to achieve the same output as with the first query?

Thank you very much.
Diana

P.S. Does anybody know how to display tables in this forum more efficiently?

 

[odin1701]

Table 1

Field 1     Field 2     Field 3     Field 4
Data        Data        Data        Data
More        More        More        More

That answers how to show tables more cleanly.

As for your database, it looks to me like there is an issue in tblRelationships.

If you relate the old orders table to the Article (A) you would get two results (A1, A2) - is that what you want, because it doesn't seem like it, but hard to tell with this info.

 

[DianaW]

Thank you very much for your reply. This is the output I want to get:

Order#   Article#       Description
1           A               Text1
2           B               Text2
3           A               Text1

Although tbOrders uses the new Article#New, the result of my query should give the "old" Article#.

I hope it has become somewhat clearer now...

 

[odin1701]

SELECT tblOrders.[Order#], tblRelationships.[Article#], tblDescriptionArticle.Description
FROM (tblOrders INNER JOIN tblRelationships ON tblOrders.[Article#New] = tblRelationships.[Article#New])
INNER JOIN tblDescriptionArticle ON tblRelationships.[Article#] = tblDescriptionArticle.[Article#]
ORDER BY tblOrders.[Order#];

 

[DianaW]

Thank you!

Diana