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.
三个表,tableA,tableB和一个联结表A_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....
我知道如何使用密钥和所有设置关系,但是当执行INSERT,UPDATE和DELETE查询时,我会感到有些困惑....
Basically, what I am looking for is an example that shows:
基本上,我正在寻找的是一个示例,显示:
-
How to get all records in TableA, based on an ID in TableB
如何根据TableB中的ID获取TableA中的所有记录
-
How to get all records in TableB, based on an ID in TableA
如何根据TableA中的ID获取TableB中的所有记录
3 How to INSERT in either TableA or TableB, and then make the appropriate INSERT in the junction table to make the connection..
3如何在TableA或TableB中插入,然后在联结表中进行相应的INSERT以建立连接。
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?
我不是在寻找特定项目的解决方案,只是可以应用的一些常规示例。也许你有什么东西躺在身边?
4 个解决方案
#1
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.
我要做的第一件事是建议使用像Linq-To-Sql或NHibernate这样的ORM,它将为您提供数据模型的对象表示,这使得处理复杂事物(如多对多CRUD操作)变得更加简单。
If an ORM isn't part of your tool set then here is how this would look in SOL.
如果ORM不是您工具集的一部分,那么这就是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
基于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
基于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)
#2
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 (...)
#3
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)
要获得表A中基于B中键的所有记录(英文版),您需要表A中具有该TableB键的Join记录的记录(假设tableA_B有两个外键列,(TabAFK和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
要插入新记录,请根据需要在TableA和TableB中进行正常插入...插入连接表(tableA_B)只是两个主表中的两个pks
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...
- 然后为每个存在的关联插入Join表...
Insert tableA_B (TabAFK, TabBFK) Values(@PkFromA, @PkFromB)
#4
1) select tableA.* from tableA join tableA_B on tableA.id = tableA_B.idA where tableA_B.idB = somevalue
1)在tableA.id = tableA_B.idA上从tableA连接tableA_B中选择tableA。*,其中tableA_B.idB = somevalue
2) select tableB.* from tableB left join tableA_B on tableB.id = tableA_B.idB where tableA_B.idA = somevalue
2)从tableB左侧选择tableB。*,在tableB.id = tableA_B.idB上连接tableA_B,其中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.
3)insert取决于你的数据库,但插入a,插入b,然后插入a_b;即使对表格有约束,它也应该以这种方式工作。
hint: don't use IN operator for 1/2
提示:不要使用IN运算符1/2
#1
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.
我要做的第一件事是建议使用像Linq-To-Sql或NHibernate这样的ORM,它将为您提供数据模型的对象表示,这使得处理复杂事物(如多对多CRUD操作)变得更加简单。
If an ORM isn't part of your tool set then here is how this would look in SOL.
如果ORM不是您工具集的一部分,那么这就是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
基于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
基于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)
#2
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 (...)
#3
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)
要获得表A中基于B中键的所有记录(英文版),您需要表A中具有该TableB键的Join记录的记录(假设tableA_B有两个外键列,(TabAFK和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
要插入新记录,请根据需要在TableA和TableB中进行正常插入...插入连接表(tableA_B)只是两个主表中的两个pks
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...
- 然后为每个存在的关联插入Join表...
Insert tableA_B (TabAFK, TabBFK) Values(@PkFromA, @PkFromB)
#4
1) select tableA.* from tableA join tableA_B on tableA.id = tableA_B.idA where tableA_B.idB = somevalue
1)在tableA.id = tableA_B.idA上从tableA连接tableA_B中选择tableA。*,其中tableA_B.idB = somevalue
2) select tableB.* from tableB left join tableA_B on tableB.id = tableA_B.idB where tableA_B.idA = somevalue
2)从tableB左侧选择tableB。*,在tableB.id = tableA_B.idB上连接tableA_B,其中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.
3)insert取决于你的数据库,但插入a,插入b,然后插入a_b;即使对表格有约束,它也应该以这种方式工作。
hint: don't use IN operator for 1/2
提示:不要使用IN运算符1/2