Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    33

    Unanswered: Stuck on Subquery of a view

    I have been trying to do a subquery on a view all morning with no luck. I will simplify my problem with a generic table below if someone could help me get my expected result set.

    Code:
    dbo.MyTableView
    --------------------------------------------------------------------------
    Date:                 Product:                          CustomerID:
    1/1/2011             Soap                               1
    1/4/2011             Coffee                             1
    1/4/2011             Tea                                 1
    1/4/2011             Shoes                              2
    1/4/2011             Ball                                  2
    1/7/2011             Orange                             2
    1/8/2011             Soap                                3
    1/8/2011             Car                                  3
    
    
    Result Set I need:    // see all customer orders where they bought soap
    ----------------------------------------------------------------------------
    1/1/2011             Soap                               1
    1/4/2011             Coffee                             1
    1/4/2011             Tea                                 1
    1/8/2011             Soap                                3
    1/8/2011             Car                                  3
    
    // Customer 2 did not buy soap I don't want any of his info...
    
    Below is what I thought I needed to write to get the expected result set:
    
    Select * from dbo.MyTableView
    Where  CustomerID IN (
           Select CustomerID from dbo.MyTableView where Proaduct = 'Soap')
    Where is my thought process off?

  2. #2
    Join Date
    Aug 2008
    Posts
    147
    select * from dbo.MyTableView where Product = 'Soap'

    what you're doing is returning all customers who have purchased soap , but they have also purchased other items . You have said customerid = 2 is in your resultset , but I cannot see customer id = 2 in your resultset
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  3. #3
    Join Date
    Jun 2010
    Posts
    33

    Customer 2

    I do not want customer 2 in the result set, since they did not purchase soap. I need all customer 1 and customer 2 items since one of their purchases was soap.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Your problem description is confusing.

    If what you want is:
    I need all customer 1 and customer 2 items since one of their purchases was soap.
    (The 2 is a typo I think, it should be "3".) Then your original query is correct. And then there was no reed to post your question in the first place.

    So, can you restate your problem? Show us the result set you got from your query and indicate where it differs from what you wanted.

    I have not executed your query, but it looks OK to me. And it should give you the "Result Set I need: // see all customer orders where they bought soap" result.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jun 2011
    Posts
    1

    Product Field is misspelled

    Select CustomerID from dbo.MyTableView where Proaduct = 'Soap'

    Select CustomerID from dbo.MyTableView where Product = 'Soap')

  6. #6
    Join Date
    Jun 2010
    Posts
    33
    sorry had a few typos. I need to revisit my issue. My thought process is correct but something is failing along the way. I am generating a report in BIDS (Business Intelligence Development Studio) which is where I think the problem is coming in at. I will post again with some concrete details.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •