?
Solved

How to create a SQL Query

Posted on 2007-11-28
2
Medium Priority
?
181 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 2000 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

749 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