SQL query doubts Join Tables

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

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

SQL

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

I need customer received mobile details and not received details

Thanks
Suresh

Posted 28-Sep-13 4:59am

itsureshuk

Updated 28-Sep-13 5:02am

v3

Add a Solution

Comments

Azee 28-Sep-13 12:11pm

Are you sure this is the output that you expect based on the data in the tables? I strongly suggest you to recheck.
What I understood is that you want details of ProductModels, whether they have been purchased by a Customer or not and if yes then the ID of the Customer.
So, in your case 'nokia 6300' have been purchased by two customers so there should be two records for this model in your output and since 'Samsung Note' haven't been purchased by anyone there isn't supposed to be a CustomerId against this record in your Output. Please verify.

itsureshuk 28-Sep-13 12:35pm

output is for only one customer (Customer DI=1)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Azee** · Accepted Answer · 2013-09-28T06:43:00

Solution 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.

SQL

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

I hope it helps

Good luck.

Azee...

Posted 28-Sep-13 6:43am

Azee

Comments

itsureshuk 29-Sep-13 6:38am

Its working fine,,,But Sales Details table will not have customer Id,,I need to take customer id from Sales Master,For that what changes i have to do.
<pre lang="SQL">

create table ProductMaster
(
ProdMasId int primary key,
ProdMasName varchar(30)
)

create table ProductModel
(
ProdModelId int primary key,
ProdMasId int references ProductMaster(ProdMasId),
ProdModName varchar(30)
)

create table CustomerDetails
(
Customerid int primary key,
CustName varchar(30)
)

create table SalesMaster
(
SalesId int primary key,
SalesAmount decimal(12,0),
Customerid int references CustomerDetails(Customerid),
ExpirysDate datetime
)

create table SalesDetails
(
SalesDetailsId int primary key,
SalesId int references SalesMaster(SalesId),
ProdModelId int references ProductModel(ProdModelId)
)
</pre>

Azee 29-Sep-13 7:05am

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

itsureshuk 30-Sep-13 14:10pm

Thanks a lot...can u suggest me some good websites for learning sql