locked
Only Return Records Where Count < 2 RRS feed

  • Question

  • This is what I am needing to do, join the two tables and only return records where the count of email address in table #existingcustomer is < 2 or non-existant.  I am working with SQL Server 2008

    I tried this syntax but receive compile error

    Create Table #OrderData
    (
    	customer varchar(100)
    	,custemail varchar(100)
    )
    
    Create Table #existingcustomer
    (
    	email varchar(100)
    )
    
    Insert Into #OrderData Values
    ('Red', 'Frank@1234.com'), ('Blue', 'George@123.com'), ('Green', 'Art@1234.com')
    
    Insert Into #existingcustomer Values
    ('Frank@1234.com'), ('Frank@1234.com'), ('black@1234.com'), ('green@1234.com')
    
    Select od.* 
    FROM #OrderData od
    INNER JOIN #existingcustomer ec
    ON od.custemail = ec.email
    WHERE COUNT(ec.email) < 2
    
    Drop Table #orderdata
    Drop Table #existingcustomer

    Monday, December 14, 2015 6:27 PM

Answers

  • Try

    Create Table #OrderData
    (
    	customer varchar(100)
    	,custemail varchar(100)
    )
    
    Create Table #existingcustomer
    (
    	email varchar(100)
    )
    
    Insert Into #OrderData Values
    ('Red', 'Frank@1234.com'), ('Blue', 'George@123.com'), ('Green', 'Art@1234.com')
    
    Insert Into #existingcustomer Values
    ('Frank@1234.com'), ('Frank@1234.com'), ('black@1234.com'), ('green@1234.com')
    
    ;with cte as (Select od.*, COUNT(ec.email) over(partition by od.Customer) as cntEmails 
    FROM #OrderData od
    LEFT JOIN #existingcustomer ec
    ON od.custemail = ec.email)
    
    select * from cte where cntEmails < 2;
    
    Drop Table #orderdata
    Drop Table #existingcustomer


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Eric__Zhang Tuesday, December 15, 2015 1:42 AM
    Monday, December 14, 2015 6:45 PM
  • Create Table #OrderData
    (
    	customer varchar(100)
    	,custemail varchar(100)
    )
    
    Create Table #existingcustomer
    (
    	email varchar(100)
    )
    
    Insert Into #OrderData Values
    ('Red', 'Frank@1234.com'), ('Blue', 'George@123.com'), ('Green', 'Art@1234.com')
    
    Insert Into #existingcustomer Values
    ('Frank@1234.com'), ('Frank@1234.com'), ('black@1234.com'), ('green@1234.com')
    
    Select ec.email from (select * FROM  #existingcustomer ec
    group by ec.email
    having COUNT(ec.email) < 2)  ec Left join  
      #OrderData od
    ON od.custemail = ec.email
     
    
    
    
    Drop Table #orderdata
    Drop Table #existingcustomer

    Monday, December 14, 2015 6:40 PM

All replies

  • You need a GROUP BY and HAVING clause.

    DECLARE @OrderData TABLE (customer varchar(100), custemail varchar(100))
    DECLARE @existingcustomer TABLE (email varchar(100))
    
    INSERT INTO @OrderData (customer, custemail) VALUES 
    ('Red', 'Frank@1234.com'), ('Blue', 'George@123.com'), ('Green', 'Art@1234.com')
    
    Insert Into @existingcustomer (email) VALUES
    ('Frank@1234.com'), ('Frank@1234.com'), ('black@1234.com'), ('green@1234.com')
    
    SELECT od.custemail, COUNT(ec.email) AS cnt
      FROM @OrderData od
        LEFT OUTER JOIN @existingcustomer ec
          ON od.custemail = ec.email
     GROUP BY od.custemail
    HAVING COUNT(ec.email) < 2



    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    Monday, December 14, 2015 6:40 PM
  • Create Table #OrderData
    (
    	customer varchar(100)
    	,custemail varchar(100)
    )
    
    Create Table #existingcustomer
    (
    	email varchar(100)
    )
    
    Insert Into #OrderData Values
    ('Red', 'Frank@1234.com'), ('Blue', 'George@123.com'), ('Green', 'Art@1234.com')
    
    Insert Into #existingcustomer Values
    ('Frank@1234.com'), ('Frank@1234.com'), ('black@1234.com'), ('green@1234.com')
    
    Select ec.email from (select * FROM  #existingcustomer ec
    group by ec.email
    having COUNT(ec.email) < 2)  ec Left join  
      #OrderData od
    ON od.custemail = ec.email
     
    
    
    
    Drop Table #orderdata
    Drop Table #existingcustomer

    Monday, December 14, 2015 6:40 PM
  • Try

    Create Table #OrderData
    (
    	customer varchar(100)
    	,custemail varchar(100)
    )
    
    Create Table #existingcustomer
    (
    	email varchar(100)
    )
    
    Insert Into #OrderData Values
    ('Red', 'Frank@1234.com'), ('Blue', 'George@123.com'), ('Green', 'Art@1234.com')
    
    Insert Into #existingcustomer Values
    ('Frank@1234.com'), ('Frank@1234.com'), ('black@1234.com'), ('green@1234.com')
    
    ;with cte as (Select od.*, COUNT(ec.email) over(partition by od.Customer) as cntEmails 
    FROM #OrderData od
    LEFT JOIN #existingcustomer ec
    ON od.custemail = ec.email)
    
    select * from cte where cntEmails < 2;
    
    Drop Table #orderdata
    Drop Table #existingcustomer


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Eric__Zhang Tuesday, December 15, 2015 1:42 AM
    Monday, December 14, 2015 6:45 PM