Sql-server – SQL Server 2012 Exclude a value

sql serversql-server-2012

I'm teaching myself SQL, and I ran into a scenario that I cannot seem to solve. Please any help.

I have a table called tblRecord with a column called BuyerID, in this case there are two BuyerIDS, and I have another table called tblOffer that has a column that is called InsertedID.

What I'm trying to do is if the two declared BuyerID from tblRecord are not the same as tbloffer in insertedID, then exclude that value.

I'm using SQL Server 2012 Management Studio. 

SELECT  
    COUNT(DISTINCT r.RecordID) AS Lines,
    COUNT(r.RecordID) AS Offers,
    MONTH(r1.Submitdate) AS M,
    DAY(r1.submitdate) AS D
FROM    
    tblReq r1
INNER JOIN 
    tblRecord r ON r1.ReqID = r.ReqID
INNER JOIN 
    tblOffer o ON r.RecordID = o.RecordID
WHERE   
    r.BuyerID IN (36, 160499)
    AND o.OfferTypeID IN (1, 3)
    AND r1.Submitted = 1
    AND r1.SubmitDate >= '2015-07-01'
GROUP BY 
    MONTH(r1.Submitdate), DAY(r1.submitdate);

Best Answer

If my understanding of your question is correct and you are only wanting to return records from tbloffer if there are also records in tblRecord matching those buyer ids, then you should be able to add a clause within your join so that it will only return those records.

I think this will cover it for you.

SELECT  COUNT(DISTINCT r.RecordID) AS Lines
      , COUNT(r.RecordID) AS Offers
      , MONTH(r1.Submitdate) AS M
      , DAY(r1.submitdate) AS D
FROM    tblReq r1
        INNER JOIN tblRecord r
            ON r1.ReqID = r.ReqID
        INNER JOIN tblOffer o
            ON r.RecordID = o.RecordID
            AND r.BuyerID = o.InsertedID
WHERE   r.BuyerID IN (36, 160499)
        AND o.OfferTypeID IN (1, 3)
        AND r1.Submitted = 1
        AND r1.SubmitDate >= '2015-07-01'
GROUP BY MONTH(r1.Submitdate)
      , DAY(r1.submitdate);

Hopefully I am understanding your question correctly and this is able to help you.

Related Question