Query examples in a many-to-many relationship

Question

Wow, it's hard to find a simple explanation to this topic. A simple many-to-many relationship.

Three tables, tableA, tableB and a junction tableA_B.

I know how to set up the relationship, with keys and all, but I get a little confused when time comes to perform INSERT, UPDATE and DELETE queries....

Basically, what I am looking for is an example that shows:

1. How to get all records in TableA, based on an ID in TableB

2. How to get all records in TableB, based on an ID in TableA

3 How to INSERT in either TableA or TableB, and then make the appropriate INSERT in the junction table to make the connection..

I'm not looking for a solution to a specific project, just a few general examples that can be applied. Maybe you have something lying around?

Answer 1

SELECT *
FROM a
WHERE id IN (SELECT aid FROM ab WHERE bid = 1234)

or

SELECT a.*
FROM a
JOIN ab ON a.id = ab.aid
WHERE ab.aid = 12345

To insert, that depends on your database (eg whether the primary keys are from sequences, auto-generated or generated in some other fashion or simply composite keys). But you just need:

For that data:

INSERT INTO a VALUES (...)

For the relationship:

INSERT INTO ab VALUES (...)

Answer 2

1) select tableA.* from tableA join tableA_B on tableA.id = tableA_B.idA where tableA_B.idB = somevalue

2) select tableB.* from tableB left join tableA_B on tableB.id = tableA_B.idB where tableA_B.idA = somevalue

3) insert depends on your database, but insert into a, insert into b, and then insert into a_b; even with constraints on the tables it should work that way.

hint: don't use IN operator for 1/2

Answer 3

The first thing I would do is recommend using an ORM like Linq-To-Sql or NHibernate which will give you object representations of your data-model which make it much simpler to handle complex things like many-to-many CRUD operations.

If an ORM isn't part of your tool set then here is how this would look in SOL.

Users       UserAddresses     Addresses
=======     =============     =========
Id          Id                Id
FirstName   UserId            City
LastName    AddressId         State
                              Zip

Our tables are joined like this:

   Users.Id -> UserAddresses.UserId
   Addresses.Id -> UserAddresses.AddressId

• All records in Users based on Addresses.Id

SELECT        Users.*
FROM            Addresses INNER JOIN
                         UserAddresses ON Addresses.Id = UserAddresses.AddressId INNER JOIN
                         Users ON UserAddresses.UserId = Users.Id
WHERE        (Addresses.Id = @AddressId)

• All records in Addresses based on Users.Id

SELECT        Addresses.*
FROM            Addresses INNER JOIN
                         UserAddresses ON Addresses.Id = UserAddresses.AddressId INNER JOIN
                         Users ON UserAddresses.UserId = Users.Id
WHERE        (Users.Id = @UserId)

Answer 4

To get all records in table A based on key in B, in english, you want the records in Table A which have a Join record with that TableB key (Assume tableA_B has two Foreign Key cols, (TabAFK and TabBFK)

  Select * from TableA A
  Where pK In (Select Distinct TabAFK From tableA_B
                Where TabBFK = @TableBKeyValue)

Same thing for other direction

  Select * from TableB B
  Where pK In (Select Distinct TabBFK From tableA_B
                Where TabAFK = @TableAKeyValue)

To insert a new record, do a normal insert into TableA and TableB as necessary... Inserts into the join table (tableA_B) are just the two pks from the two main tables

   Insert TableA (pk, [other columns]) Values(@pkValue,  [other data)
   Insert TableB (pk, [other columns]) Values(@pkValue,  [other data)

---

-- Then insert into Join table for each association that exists...

  Insert tableA_B (TabAFK, TabBFK)  Values(@PkFromA,  @PkFromB)

Answer 5

This is a follow up question, regarding updating the junction table. To do this, I would have to use both key values in the junction table, in the WHERE clause.

Users       UserAddresses     Addresses
=======     =============     =========
FirstName   UserId            City
LastName    AddressId         State
                              Zip

In this example, for instance, say I wanted to update the AddressId field in the UserAddresses table, because a user changed his address. I would have to use both the existing UserId and the address AddressId in the update WHERE clause.

I'm using a stored procedure, and passing in UserId and the new AddressId as parameters.

I've tries this:

CREATE PROCEDURE dbo.test
(
@UserId int,
@AddressId int
)

AS

create table #temp
     (
     UserId int not null,
     AddressId int not null
     )
     insert into #temp select UserId, AddressId from UserAddresses where UserId = @UserId

update UserAddresses
set AddressId = @AddressIdD

WHERE (UserId+AddressId in #temp table = UserId+AddressId passed in as parameters)??

I've tried all sorts of combinations, but I can't seem to get the syntax right.

The UserId passed in, would ofcourse be the same as the one in the UserAddresses table, but this is just me trying some things. The WHERE clause is where it seems to go wrong.

Any thoughts?