Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    25

    Unanswered: Help with comparing a table with itself?

    I have a problem I am working. The question is as follows:

    Write a query that produces all pairs of orders by a given customer, names that customer, and eliminates duplicates.

    ===Answer:
    select cname, first.onum, second.onum
    from ORDERS first, ORDERS second, CUSTOMERS
    where first.cnum = second.cnum --link cnums of same table? why do this?
    and first.onum < second.onum -- find differences between onum values? but why if your comparing the same table?
    and first.cnum = CUSTOMERS.cnum --link orders to customers for cnum

    I tried to document the answer and show what I was thinking. Could someone please help me figure out what this is doing?

    Andrew

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    don't understand what you are doing

    but I can't see any Joins in your select statement.

    if you group by the Customer and select the Min(first) of the Orders
    that give you the first order of each Customer
    them write and other one to give the the Max(last) of the Orders

    now join the other query by the Customer and show the MAX(last) and the Min(first)
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Mar 2013
    Posts
    25

    Clarifications

    Maybe this additional information will help? Sorry for the poor details. I will now share everything I have. This comes from page 97.

    I am reading a book called, Understanding SQL by Martin Gruber.

    Here are my notes with excerpts from the book. I am providing question 1 below since its tied to question 2. But question 2 is what I am trying to understand

    chapt 9

    1 - write a query that produces all pairs of salespeople who are living
    in the same city. exclude combinations of salespeople with themselves
    as well as duplicate rows with the order reversed

    ++Answer++
    select first.sname, second.sname
    from salespeople first, salespeople second
    where first.city = second.city
    and first.sname < second.sname;



    2 - write a query that produces all pairs of orders by a given customer,
    names that customer, and eliminates duplicates, as above in question 1 above.

    select cname, first.onum, second.onum
    from ORDERS first, ORDERS second, CUSTOMERS
    where first.cnum = second.cnum --link cnums of same table
    and first.cnum = CUSTOMERS.cnum --link cnum table to customers table
    and first.onum < second.onum; -- find differences between onum values

    You can change the < or > sign it makes no diff. 8 rows are returned either way with the same
    info

    Hope this helps?
    Andrew

  4. #4
    Join Date
    Mar 2013
    Posts
    25

    Something strange about self join

    I did a self join on my orders table and noticed that the table has 14 rows but when I did a self join on onum in the orders table I got 30 rows! I have no idea why that happened?

    Andrew
    Attached Thumbnails Attached Thumbnails orders table.png  

Posting Permissions

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