Many to many relationship

[Fuma]

I have 2 tables (Table_A and Table_B) that have a many-to-many relationship. I create a relationship table called Table_AB

[U][B]Table_[/B]A[/U]
-A_id (PK)
.
.
.
.

[U][B]Table_A[/B]B[/U]
-A_id (PK,FK)
-B_id (PK,FK)


[U][B]Table_B[/B][/U]
-B_id (PK)
.
.
.
.

1. When I insert into both Table_A and Table_B, do I also have to insert into the relationship table or does it automatically resolve itself?
   

   insert into Table_A(A_id) values ('A0001')

   insert into Table_B(B_id) values ('B00001')

2. What will be my sql when I want to select from these two tables? Will it be:
   

   select a.A_id, b.B_id from Table_A a, Table_b b, Table_AB ab 
   WHERE a.A_id = ab.A_id AND
    b.B_id =ab.B_id

EDIT: I think I have to insert into the relationship table.
BUT my second question still stands.

 

[guest2013-1]

You'd have to insert the records yourself.

For example, you sell DVD's. You have a "Movie Category" table and a "Movie" table

Now a movie can be a Horror Suspense (for example)

So instead of creating a "Horror Suspense" category, you'd have "Horror" and "Suspense" as two seperate categories which you now will link to the movie. But you can't just add two ID's to the movie table, so that's where your "many to many" relationship table comes into play.

Now. In that scenario, I'm able to add a Movie OR a category, but neither will know it exists UNLESS you add the new Movie ID and the new Category ID into the "many to many" relationship table to "link" the records.

So now 1 movie can have multiple categories.

Your select statement to select this would look more or less like:

select Category,Movie from tbl_Movies m inner join tbl_Movie_Category mc on mc.MovieID = m.MovieID inner join tbl_Category c on c.CategoryID = mc.CategoryID

That should get you the category and movie name. NB, the output would look like:

Hardcore, My nipples are hard
Boobs, My nipples are hard
Bouncy, My nipples are hard


For example

 

[guest2013-1]

Added a bold for you, obviously half reading my post

 

[Fuma]

Thanks.

I have a few thousands records in Table_A and I only need few hundreds - same with Table B.
I guess I have to manually insert into this "link" table. Eish

 

[guest2013-1]

Eish indeed. You'd need to have the code in place before launching something like this my friend.

That's why I'll always and forever say "The database is the foundation, so design it right else you'll pay for it dearly later"

Depending on what data you're talking about and if there's specific markers in both tables to identify which record is linked to which then you could probably just write a t-sql statement to take care of that for you. this is why it's important to build relationships between tables from the beginning

 

[SlinkyMike]

For example, you sell DVD's. You have a "Movie Category" table and a "Movie" table

Now a movie can be a Horror Suspense (for example) /snip...

Fantastic example!

I have been and seen this taught several times and this is the finest example that I have come across - gold star for you!

 

[guest2013-1]

Fantastic example!

I have been and seen this taught several times and this is the finest example that I have come across - gold star for you!

lol, thanks... something gave you the idea I've taught this before huh? Well you'd be right. Oh and I was taking the movie example because of an online DVD rental website I did a few years back and I think everyone can associate with movies

 

[Saajid]

Nice explanation. To the OP, if you're savvy enough, you could right a little script, or even a little app to fill up the relationship table for you... Doing it manually will be a pain in the ass. Automation is the key to success!

 

[Fuma]

Eish indeed. You'd need to have the code in place before launching something like this my friend.

That's why I'll always and forever say "The database is the foundation, so design it right else you'll pay for it dearly later"

Depending on what data you're talking about and if there's specific markers in both tables to identify which record is linked to which then you could probably just write a t-sql statement to take care of that for you. this is why it's important to build relationships between tables from the beginning

I know that, but unfortunately I didn't design the DB. I'm redesigning it bit by bit as I go along. Just haven't done big db stuff for some time.

 

[greggpb]

Nice explanation. To the OP, if you're savvy enough, you could right a little script, or even a little app to fill up the relationship table for you... Doing it manually will be a pain in the ass. Automation is the key to success!

I recon this is only way to do it.. it would be really simple with a "insert into (select from)" statement

 

[Yucca]

Eish indeed. You'd need to have the code in place before launching something like this my friend.

That's why I'll always and forever say "The database is the foundation, so design it right else you'll pay for it dearly later"

Depending on what data you're talking about and if there's specific markers in both tables to identify which record is linked to which then you could probably just write a t-sql statement to take care of that for you. this is why it's important to build relationships between tables from the beginning

Actually it's a matter of preference. It used to be create the tables that represent your domain objects first but in recent times it has become far less verbose (in JEE at least to map relationships in code with annotations and orm an generate tables from Entity beans