以多对多关系查询示例 [英] Query examples in a many-to-many relationship

问题描述

哇，很难找到对此主题的简单解释.一个简单的多对多关系.

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

三个表，即tableA，tableB和一个联结表A_B.

Three tables, tableA, tableB and a junction tableA_B.

我知道如何使用键和全部来建立关系，但是当需要执行INSERT，UPDATE和DELETE查询时，我会有些困惑.

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. 如何基于TableB中的ID获取TableA中的所有记录

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

如何基于TableA中的ID获取TableB中的所有记录

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

3如何在TableA或TableB中插入，然后在联结表中进行适当的INSERT以建立连接.

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?

推荐答案

我要做的第一件事是建议使用 NHibernate 这将为您提供数据模型的对象表示，这使处理诸如多对多CRUD操作之类的复杂事情变得更加简单.

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.

如果ORM不属于您的工具集，那么这就是在SOL中的外观.

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

• 用户中基于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)

• 地址中基于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)

这篇关于以多对多关系查询示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！