Many-to-many relationships - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Many-to-many relationships

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-17 : 09:09:55

Chad writes "I have three tables (see below) where there is a many-to-many relationship. A single photo can contain several models and those models can also show up on other photos.

Table 1. Photos
Fields: PhotoID, Description

Table 2. Models
Fields: ModelID, ModelName

Table 3. ModelLink
Fields: ModelLinkID, PhotoID, ModelID

How do I query (select) a photo and display the PhotoID, Description, and all of the corresponding ModelNames?

Thanks!"

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2001-12-17 : 09:38:33

Are you sure you need those many-to-many relationships. Can you not have photos to models (1 to many). It's hard to fathom exactly how your relationships are gelling together here but i can't help thinking there's an easy 1 to many solution within it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-17 : 10:17:07

To me, that's what the ModelLink table provides: associating a photo to the model(s) in the photo. I think the structure you have now is fine.

Now I know I'll get in trouble again because I'm not supposed to talk about primary keys

but the ModelLinkID column isn't really necessary. PhotoID and ModelID would be unique combinations (a model can't appear twice in the same photo...unless you use some special effects...and even if he/she did, they're still the same person!) I bet that ModelLinkID won't relate to any other tables, so you could discard it and use PhotoID and ModelID as primary key on the ModelLinkTable.

Edited by - robvolk on 12/17/2001 10:17:56

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2001-12-17 : 10:28:43

I agree with Rob on both counts. Your query should look like this...


select p.photoid, p.description, m.modelname
from photos p inner join modellink ml on p.photoid = ml.photoid
inner join models m on ml.modelid = m.modelid

Justin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources