Solved

How to create a SQL Query

Posted on 2007-11-28
2
161 Views
Last Modified: 2010-04-21
HI,

I have 3 tables with the following fields
1) ProcessorProduct
    fields:    ProductID  INT IDENTITY(1,1) NOT NULL,  
                 OnCatalogPromotion BIT NOT NULL,
                 DealReasonType INT,  
                 RebateID INT ....
2) DealReason
    fields:    DealReasonType INT IDENTITY(1,1) NOT NULL,
                  Name VARCHAR 50) NOT NULL,
                  Description VARCHAR 200 ...
3) Rebate
      fileds:   RebateID INT IDENTITY(1,1) NOT NULL,
                   RebateName VARCHAR(50) NOT NULL,
                   RebateValue  MONEY NOT NULL  .....

ProductTable has a many-to-many relation with DealReason
ProductTable has a one-to-many relation with Rebate table

I need to find all the records in ProductTable that are true for OnCatalogPromotion field
Also, find the DealReason record that goes with the current ProductTable record and
Find the Rebate record that goes with the current ProductTable
I have the following

SELECT ProcessorProduct.ProductID,
                ProcessorProduct.Name,
                DealReason.Name,
FROM ProcessorProduct INNER JOIN ProductDealReason
    ON ProcessorProduct.ProductID = ProductDealReason.ProductID  
 INNER JOIN DealReason
    ON ProductDealReason.DealReasonType = DealReason.DealReasonType
WHERE ProcessorProduct.OnCatalogPromotion = 1

The above query is find but now I need to find the fields Rebate.Name and Rebate.RebateValue
for the ProcessorProduct table records. I don't know if this should be done in another query or wether it should be embeded into the above query. I try to do the following but does not work because the subqueries cannot be done on multiple fields

SELECT ProcessorProduct.ProductID,
                ProcessorProduct.Name,
                DealReason.Name,
               (SELECT Rebate.RebateName, Rebate.RebateValue
                 FROM Rebate
                 WHERE Rebate.RebateID = ProcessorProduct.RebateID)
FROM ProcessorProduct INNER JOIN ProductDealReason
    ON ProcessorProduct.ProductID = ProductDealReason.ProductID  
 INNER JOIN DealReason
    ON ProductDealReason.DealReasonType = DealReason.DealReasonType
WHERE ProcessorProduct.OnCatalogPromotion = 1

In this query I am trying to get the Name and RebateValue fields from Rebate table. Please help, I am getting the following error in SQL Server

Only one expression can be speciefied in the select list when the query is not introduced with EXISTS

SELECT ProcessorProduct.ProductID, 
                ProcessorProduct.Name,
                DealReason.Name,
               (SELECT Rebate.RebateName, Rebate.RebateValue 
                 FROM Rebate
                 WHERE Rebate.RebateID == ProcessorProduct.RebateID)
FROM ProcessorProduct INNER JOIN ProductDealReason
    ON ProcessorProduct.ProductID = ProductDealReason.ProductID  
 INNER JOIN DealReason 
    ON ProductDealReason.DealReasonType = DealReason.DealReasonType
WHERE ProcessorProduct.OnCatalogPromotion = 1

Open in new window

0
Comment
Question by:vielkacarolina1239
2 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
ID: 20366859
You need to join the Rebate table. I've used LEFT JOIN to make sure that rows are retrieved from the other tables even when there are no matching rows in Rebate

SELECT ProcessorProduct.ProductID,
                ProcessorProduct.Name,
                DealReason.Name,
               Rebate.RebateName, Rebate.RebateValue
FROM ProcessorProduct INNER JOIN ProductDealReason
    ON ProcessorProduct.ProductID = ProductDealReason.ProductID  
 INNER JOIN DealReason
    ON ProductDealReason.DealReasonType = DealReason.DealReasonType
 LEFT JOIN  Rebate
    ON Rebate.RebateID = ProcessorProduct.RebateID
 
WHERE ProcessorProduct.OnCatalogPromotion = 1

0
 

Author Closing Comment

by:vielkacarolina1239
ID: 31411450
Thanks, I really appreciate it.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question