如何使用Sql或Linq To Entities从多个相关表中获取数据 [英] How Do I Get Data From A Many To Many Related Table Using Sql Or Linq To Entities

问题描述

我有两个表相关的表，这是一个多对多关系。例如：我有一个包含以下列的用户，产品和用户产品表：

I have table related to two tables which is a many to many relationship. For eg: I have a user, product and userproducts table that contains these columns:

现在，我如何分别从用户产品中获取用户和产品表中的用户名，姓氏和产品名称表。

Now, how do i get the username, last name and product name from the users and products table respectively that does not exist in the Userproducts table.

我有一个列出用户产品表中所有用户产品的方法，但实际上这给我带来了相反的方式。

I have a method that list all the users products from the Userproductstable, but it is really giving me a tough time to get it the other way around.

请关于如何在EF或纯SQL查询中执行此操作的任何帮助。谢谢

Please, any help on how to do this either in EF or pure sql query. Thanks

推荐答案

您好，



如果我理解正确，您需要所有组合用户产品表中不存在的用户和产品？



如果这是正确的，那么此查询应该会为您提供所有必需的结果：

Hello,

If I understood you correctly, you need all combinations of users and products that doesn't exist in Userproducts table?

If that is correct, then this query should give you all required results:

Select u.username, u.lastname, p.name
From User as u, Product as p
Where not exists 
(Select * From Userproducts as up Where up.userid=u.id and up.productid=p.id)

如果您需要的是用户和产品，则更容易：

用户：

If what you require is separately for users and for products, then it is even easier:
Users:

Select u.username, u.lastname
From User as u
Where u.id not in
(Select up.userid From Userproducts)

产品：

Products:

Select p.name
From Product as p
Where p.id not in
(Select up.productid From Userproducts)

希望它有所帮助。

问候，

Hope it helped.
Regards,

这篇关于如何使用Sql或Linq To Entities从多个相关表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！