Solved

How to create a SQL Query

Posted on 2007-11-28
2
163 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Currency in SQL? 2 45
Where on a calculated field 1 31
How can I find this data? 3 28
Not listening to where 1 22
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 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