SQL查询疑惑联接表 [英] SQL query doubts Join Tables

问题描述

您好，
我在数据库中有产品详细信息，客户详细信息和客户购买详细信息.我需要以下输出.这一天对我有帮助.

Hi frnds,
I have Product details,customer details and Customer Purchase details in database.i need following output.help me for the day.

Table 1: ProductMaster

ProdMasId    ProdMasName  

  1           nokia
  2           Samsung

Table 2: ProductModel
 
ProdModelId    ProdMasId    ProdModName  

      1           1           nokia 6300
      2           1           nokia Lumia
      3           2           Samsung Grand
      4           2           Samsung Note

Table 3: CustomerDetails

     Customerid    CustName  

       1           Suresh
       2           Ramesh
       3           Rajesh

Table 4: SalesMaster

       SalesId        SalesAmount  Customerid    ExpirysDate
          1             2000            1          12-12-13
          2             3000            2          01-01-14
          3             4000            1          02-01-15 

Table 5:   SalesDetails

         SalesDetailsId  SalesId   ProdModelId  CustomerId
 
              1             1          1           1
              2             1          2           1
              3             2          1           2
              4             3          3           1    

I need output as follow as for first purchase details:

       ProdModelId    ProdMasId    ProdModName       Customerid    Purchased     

          1           1           nokia 6300             1           True
          2           1           nokia Lumia            1           True
          3           2           Samsung Grand          1           True
          4           2           Samsung Note           1           False

我需要客户收到的手机详细信息，而没有收到详细信息

谢谢
Suresh

I need customer received mobile details and not received details

Thanks
Suresh

推荐答案

好吧，我假设CustomerId(1)是存储过程的参数，在我的脚本中，我声明了要使用的参数.我复制了您的方案，此脚本获得了所需的输出.

Ok I assume the CustomerId (1) is a parameter of your stored procedure, in my script I have declared one to use. I replicated your scenario and this script gets the output that you need.

Declare @CustomerId int = 1;
Select distinct PM.ProdModelId, PM.ProdMasId, PM.ProdModName, @CustomerId as CustomerId ,
Case when SM.SalesId IS NULL THEN 'false' ELSE 'true' END AS Purchased
from ProductModel PM
Left Join SalesDetails SD on PM.ProdModelId  = SD.ProdModelId and SD.CustomerID = @CustomerId
Left Join SalesMaster SM on SD.SalesID = SM.SalesID 

希望对您有帮助

祝你好运.

Azee ...

I hope it helps

Good luck.

Azee...

这篇关于SQL查询疑惑联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！