CODEWITHSUNDEEP
mysqldatabase
danwoods
danwoods

Reputation: 4907

One table which references many other id's

I know this must be a super beginner question, but how do I create a table which has a column which can reference multiple id's of other tables? i.e.: One user may have many communities, and a community belongs to several different users. Is this possible? Does the question make sense?

[edit]Is this -> Many-to-many relationship INSERT the only way?

Upvotes: 1

Views: 1684

Answers (1)

Jason Mitchell
Jason Mitchell

Reputation: 289

Normally you won't have a column in one that references multiple IDs. You'll normally setup a third table to hold that information. Let me give an example:

Say you have a table for "Pages" on a web-site

"Pages Table"
Page_ID | Page_Name
1         Home

Say you want to include some articles on those pages that are stored in another table 

"Articles Table"
Article_ID | Article_Text
1            SQL is Fun!
2            Java is no fun!

And you want to be able to include a few different articles on your page, you'd make a third table, let's call it "ArticlesInPages" with the follow columns:

"ArticlesInPages Table"
 Page_ID | Article_ID
 1         1
 1         2

You could now write SQL "SELECT * from ArticlesInPages join articles on articles.article_id = ArticlesInPages.article_id where page_id = 1"

And it would return

 Page_ID | Article_ID | Article_Text
 1         1            SQL Is Fun!        
 1         2            Javs is No Fun!

This would return both the articles, hope this helps!

Upvotes: 4

Related Questions