none
Returning Results Where Values Both EXIST & NOT EXIST From Two Tables

    Question

  • Hello All,

    I have a bit of a newbie question and i'm totally confused, here's the scenario:

    I have two tables, Individual and Sales.  There are individuals who exist in the Individual table but who do NOT exist in the Sales table (they have not purchased anything). 

    I need to write a query which returns a result set of all individuals who have sales less than $50.  I understand how to write a query to find the individuals who DO NOT EXIST in the Sales table, my problem is that I do not understand how to write a query which will return a result set containing individuals that exist in the Sales table alongside those who do not exist.  I know this is easier than I think it is, but i'm not able to figure this out. 


    Here are some queries which I have so far:

    This query will return a list of Individuals who's ID's exist in the Sales table, but who have sales less than $50.  The problem with this query is that it DOES NOT return those individuals who's ID's are not contained within the Sales table:

    SELECT I.EmailAddress, SUM(S.SalesAmount)
    FROM Individual as I
    INNER JOIN Sales as S
        ON I.IndividualID = S.IndividualID
    WHERE S.SalesAmount < 50
    GROUP BY I.EmailAddress

    Result set from this query:  amy@microsoft.com  with sales amount of $25



    The second query will return a list of individuals who's ID DO NOT exist in the Sales table:

    SELECT I.EmailAddress
    FROM Individual as I
    WHERE NOT EXISTS (SELECT Sales.IndividualID FROM Sales WHERE I.IndividualID = Sales.IndividualID)

    Result set from this query:  norman@army.mil 



    Here's what happens if I try and combine the two together into one query - I realize that my problem is probably around the Inner Join, but i'm not able to figure out why:

    SELECT I.EmailAddress, SUM(S.SalesAmount) as SalesAmount
    FROM Individual as I
    INNER JOIN Sales as S
        ON I.IndividualID = S.IndividualID
    WHERE NOT EXISTS (SELECT Sales.IndividualID FROM Sales WHERE I.IndividualID = Sales.IndividualID)
          AND S.SalesAmount < 50
    GROUP BY I.EmailAddress

    Result set from this query:  <empty set>


    Again, I need to get a result set that will include both Norman and Amy and I do not understand how.  I have consulted SQL books and also spent hours searching the internet but have not been able to find a solution. I am greatly simplifying my scenario for clarity here, but this will give you an idea of what I am trying to do.  Please let me know if this will be enough info.

    Can somone help? 

    How do you solve a problem such as this? 

    Thanks!!

    Thursday, August 07, 2008 7:59 AM

Answers

  • Stijn,

     

    I think that you have over complicated the solution by using the union. It is easily achievable by chaging the join to a left join. I might be wrong but if the criteria is to idenitfy customers with a total sales of less than 50.00 then you will need to change the where clause to a having clause.

     

    The example below demonstates this.

     

    CREATE TABLE dbo.Customer

    (

    CustName varchar (10) not null

    )

    go

    insert into dbo.Customer (CustName)

    SELECT 'John' UNION ALL

    SELECT 'Paul' UNION ALL

    SELECT 'Peter' UNION ALL

    SELECT 'Stephen'

    GO

    CREATE TABLE dbo.Sales

    (

    CustName varchar (10) NOT NULL,

    SalesValue money NOT NULL

    )

    GO

    INSERT INTO dbo.Sales (CustName, SalesValue)

    SELECT 'John', 100.00 UNION ALL

    SELECT 'Paul', 50.00 UNION ALL

    SELECT 'Paul', 100.00 UNION ALL

    SELECT 'Stephen', 175.00 UNION ALL

    SELECT 'Stephen', 100.00 UNION ALL

    SELECT 'John', 100.00

    GO

    SELECT c.CustName, COALESCE(SUM(s.SalesValue), 0.0)

    FROM

    dbo.Customer c

    LEFT JOIN

    dbo.Sales s

    ON s.CustName = c.CustName

    GROUP BY

    c.CustName

    GO

     

    Therefore, the solution would look something like:

     

    SELECT I.EmailAddress, COALESCE(SUM(S.SalesAmount), 0.0) as SalesAmount
    FROM
    Individual as I

    LEFT JOIN Sales as S
        ON I.IndividualID = S.IndividualID
    GROUP BY
    I.EmailAddress

    HAVING COALESCE(SUM(S.SalesAmount), 0.0) < 50.0

     

     

    Rick

    Thursday, August 07, 2008 8:55 AM

All replies

  • Try it like this:

    SELECT * from
    (SELECT I.EmailAddress, SUM(S.SalesAmount) as SalesAmount

    FROM Individual as I
    INNER JOIN Sales as S
        ON I.IndividualID = S.IndividualID where S.SalesAmount < 50 GROUP BY I.EmailAddress) ds
    WHERE NOT EXISTS (SELECT Sales.IndividualID FROM Sales WHERE ds.IndividualID = Sales.IndividualID)

    Thursday, August 07, 2008 8:07 AM
  • Thank you for answering so quickly!!

    Unfortunately, i'm getting an "Invalid Column Name" when I run this, it's somewhere in the WHERE NOT EXISTS clause.  I'm going to continue to investigate, but if anyone would happen to know how I could resolve this error I would greatly appreciate it.

    Thanks!!
    Thursday, August 07, 2008 8:13 AM
  • SELECT I.EmailAddress, SUM(S.SalesAmount) as SalesAmount
    FROM Individual as I
    INNER JOIN Sales as S
        ON I.IndividualID = S.IndividualID
    WHERE S.SalesAmount < 50
    GROUP BY I.EmailAddress
    UNION
    SELECT I.EmailAddress, 0
    as SalesAmount
    FROM Individual as I
    WHERE NOT EXISTS (SELECT Sales.IndividualID FROM Sales WHERE I.IndividualID = Sales.IndividualID)

    I think this is more what you're looking for.
    Thursday, August 07, 2008 8:22 AM
  • Stijn,

     

    I think that you have over complicated the solution by using the union. It is easily achievable by chaging the join to a left join. I might be wrong but if the criteria is to idenitfy customers with a total sales of less than 50.00 then you will need to change the where clause to a having clause.

     

    The example below demonstates this.

     

    CREATE TABLE dbo.Customer

    (

    CustName varchar (10) not null

    )

    go

    insert into dbo.Customer (CustName)

    SELECT 'John' UNION ALL

    SELECT 'Paul' UNION ALL

    SELECT 'Peter' UNION ALL

    SELECT 'Stephen'

    GO

    CREATE TABLE dbo.Sales

    (

    CustName varchar (10) NOT NULL,

    SalesValue money NOT NULL

    )

    GO

    INSERT INTO dbo.Sales (CustName, SalesValue)

    SELECT 'John', 100.00 UNION ALL

    SELECT 'Paul', 50.00 UNION ALL

    SELECT 'Paul', 100.00 UNION ALL

    SELECT 'Stephen', 175.00 UNION ALL

    SELECT 'Stephen', 100.00 UNION ALL

    SELECT 'John', 100.00

    GO

    SELECT c.CustName, COALESCE(SUM(s.SalesValue), 0.0)

    FROM

    dbo.Customer c

    LEFT JOIN

    dbo.Sales s

    ON s.CustName = c.CustName

    GROUP BY

    c.CustName

    GO

     

    Therefore, the solution would look something like:

     

    SELECT I.EmailAddress, COALESCE(SUM(S.SalesAmount), 0.0) as SalesAmount
    FROM
    Individual as I

    LEFT JOIN Sales as S
        ON I.IndividualID = S.IndividualID
    GROUP BY
    I.EmailAddress

    HAVING COALESCE(SUM(S.SalesAmount), 0.0) < 50.0

     

     

    Rick

    Thursday, August 07, 2008 8:55 AM
  •  RickHalliday wrote:

    Stijn,

     

    I think that you have over complicated the solution by using the union. It is easily achievable by chaging the join to a left join. I might be wrong but if the criteria is to idenitfy customers with a total sales of less than 50.00 then you will need to change the where clause to a having clause.

     

    If it's the total sales amount then indeed he should use a HAVING, otherwise he's just not taking into account all the sales that is less than $50.

     

    You are absolutely right about the left join, union is not necessary here!

    Thursday, August 07, 2008 9:07 AM
  • Rick & Stijin,

    Thank you very much for responding to my thread.  I'm still learning, so I get caught-up in over-complexity somtimes before I figure out what's going on. 

    I actually managed to figure this out on my own just as Rick posted his reply, i'm happy to report that my solution was very similar to Rick's except that I used a WHERE clause and ISNULL() while he used HAVING and COALESCE().  I think the part that was confusing me when I used a LEFT JOIN earlier was the NULL value, I got around that by using the ISNULL() function.  Still, I don't understand why this confused me so much.

    But thank you both for popping in here and assisting a newbie, it is greatly appreciated.  I actually forgot about derived tables so Stijin got me to remember those, again much appreciated. 

    Thanks!!
    Thursday, August 07, 2008 9:10 AM