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
Solved

How to create a SQL Query

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

839 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