在两个表之间的差异上的SQL表连接

时间：2021-12-24 01:58:19

I have two tables and i need to query the difference on one field on both them and then to join the result with one of the tables , those are my tables :
                                   -----------------------------------------------
Products :                 | ProductId(PK)  | ProductName     |
                                   -----------------------------------------------
                                   |          1             | Pension Funds   |
                                   -----------------------------------------------
                                   |          2             | Study Funds       |
                                   -----------------------------------------------
                                   |          3             | Provident Funds |
                                   -----------------------------------------------

                              ------------------------------------------------------------------
Comissions :       | SellerId(PK1) | ProductId(PK2)(FK) | Comission |
                              ------------------------------------------------------------------
                              |        11           |            1                    |      10        |
                              ------------------------------------------------------------------          
                              |        11           |            2                    |      20        |
                              ------------------------------------------------------------------
                              |        22           |            3                    |      30        |
                              ------------------------------------------------------------------
                              |        33           |            1                    |      10        |
                              ------------------------------------------------------------------
                              |        33           |            2                    |      20        |
                              ------------------------------------------------------------------
                              |        33           |            3                    |      30        |
                              ------------------------------------------------------------------

我有两个表,我需要查询的区别在一个字段上,然后加入结果的表,这些是我表:- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -产品:| ProductId(PK)| ProductName | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | 1 |养老基金| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | | 2 |研究基金- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - |3 |公积金| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -佣金:| SellerId(PK1)| ProductId(PK2)(颗)| |佣金- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | 10 11 | 1 | | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | 11 | 2 | 20 |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | | 3 | 30 22日| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | 33 | 1 | 10 | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -33 | | 2 | 20 | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | 33 | 3 | 30 | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

When I have a SellerId i need to query all the ProductId's and ProductNames that the seller don't sale
e.g. if given SellerId = 11 as a result I should get back one record (3,Provident Funds)
       if given SellerId = 22 as a result I should get two records (1,Pension Funds) and (2,Study Funds)
       if given SellerId = 33 no records should be returned .

当我有一个SellerId我需要查询所有的ProductId和productname,卖方不出售如如果SellerId = 11因此我应该返回一个记录(3、公积金)如果SellerId = 22结果我应该得到两个记录(1、养老基金)和(2,研究基金)如果SellerId = 33不应该返回记录。

for now I have this :

SELECT ProductId FROM Products
EXCEPT
SELECT ProductId FROM Comissions WHERE SellerId = @SellerId

现在我有了这个:从产品中选择ProductId，但是从SellerId = @SellerId的组合中选择ProductId

this query return the differnce of ProductId's , I need a help to join it with Products table on ProductId's .

这个查询返回ProductId的不同之处，我需要一个帮助来将它与ProductId的产品表连接起来。

Thanks in advance

谢谢提前

2 个解决方案

#1

3  

Try this:

试试这个:

SELECT * FROM Products
    WHERE ProductId NOT IN (SELECT ProductId FROM Comissions WHERE SellerId = @SellerId)

#2

1  

Apart from the answer given by dotnetom, this is by using Exists.

除了dotnetom给出的答案，这是通过使用存在。

SELECT *
FROM Products
WHERE NOT EXISTS (
        SELECT ProductId
        FROM Comissions
        WHERE SellerId = @SellerId
            AND ProductId = Products.ProductId
        )

SQL Fiddle

#1

3  

Try this:

试试这个:

SELECT * FROM Products
    WHERE ProductId NOT IN (SELECT ProductId FROM Comissions WHERE SellerId = @SellerId)

#2

1  

Apart from the answer given by dotnetom, this is by using Exists.

除了dotnetom给出的答案，这是通过使用存在。

SELECT *
FROM Products
WHERE NOT EXISTS (
        SELECT ProductId
        FROM Comissions
        WHERE SellerId = @SellerId
            AND ProductId = Products.ProductId
        )

SQL Fiddle

标签：

相关文章