Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to create a SQL Query

Posted on 2007-11-28
2
Medium Priority
?
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
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…

636 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