Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: Exluding records that are used in search

    I'm brand new to DB and Oracle and am stumped on this extra question for a lab at school.

    This question is taken from the Oracle textbook.

    'List the title of all books in the same category as books previously purchased by customer 1007. Do not include books this customer has already purchased.'

    What exactly is being asked here... this is where I was going, but somehow it seems not quite right.

    SELECT title, customer#, category
    FROM books b JOIN orderitems oi
    ON b.isbn = oi.isbn
    JOIN orders o
    ON oi.order#= o.order#
    WHERE title =ANY (SELECT title
    FROM books b JOIN orderitems oi
    ON b.isbn = oi.isbn
    JOIN orders o
    ON oi.order#= o.order#
    WHERE customer# = 1007);

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here are some hints:

    Category / categories of books previously purchased by customer 1007:
    Code:
    select category
    from books
    where customer = 1007
    Books purchased by all customers, having the same category as 1007's books:
    Code:
    select title
    from books
    where category in <category of books previously
                       purchased by customer 1007>;
    Exclude 1007's books:
    Code:
    where title not in (select title 
                        from books
                        where customer = 1007)
    Now your turn: try to make one query from these three, and you might get the solution.

  3. #3
    Join Date
    Apr 2010
    Posts
    2
    Thanks for that! Got it!
    Code:
    SELECT title
    FROM books
    WHERE category in (SELECT category
                      FROM books b JOIN orderitems oi
                      ON b.isbn = oi.isbn
                      JOIN orders o
                      ON oi.order#= o.order# 
                      WHERE customer# = 1007)
    AND title not in (SELECT title
                      FROM books b JOIN orderitems oi
                      ON b.isbn = oi.isbn
                      JOIN orders o
                      ON oi.order#= o.order# 
                      WHERE customer# = 1007);
    Can you break down how you pulled out those 3 things that were needed? I think that's where I am running into an issue.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How? I read the question, that's how.

    one
    two
    three

    'List the title of all books in the same category as books previously purchased by customer 1007. Do not include books this customer has already purchased.'

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    These steps are what many fail to see in real life coding situations and LittlePiggies has highlighted the main steps perfectly.

    Every piece of code for a problem can be (and should be) broken down into its smallest steps/pieces first.

    As you stated, you got confused a little because you were looking at the BIG picture.
    Always try to break down the big picture into the smallest pieces possible and construct
    your sql based on the small pieces and build outward towards complexity.
    Once you get the SQL itself working and understand all the aspects of the
    data you can then try to condense the SQL into more efficient
    pieces of code (performance tune, 1 scan vs. 3, etc).

    If you can master this breakdown process for every problem/SOW you are handed then
    you will never fail and be an invaluable asset to your company.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Little ... what?!?

  7. #7
    Join Date
    Aug 2009
    Posts
    262
    deto

    where are the lightening bolts .. where is the thunder

Posting Permissions

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