Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: subquery

  1. #1
    Join Date
    Mar 2013
    Posts
    25

    Unanswered: subquery

    I am trying to solve the following problem:

    Find the largest order amount for each salesperson and the associated order number, along with the customer to whom that order belongs to. Right now all my code does is:

    Display salesperson_id more than once and display all order number and all sales amounts. What it should be doing is giving me only 1 salesperson_id with the highest sales amount and include the order number along with it.

    Any help would be greatly appreciated.


    select distinct orders.salesperson_id, Number as OrderNum, Amount
    from
    Orders
    left JOIN
    (
    SELECT salesperson_id, max(number) As MaxNum, MAX(Amount) AS MaxOrder
    FROM Orders
    GROUP BY salesperson_id, Number, Amount
    ) as TopOrderAmountsPerSalesperson
    On Orders.cust_id = TopOrderAmountsPerSalesperson.salesperson_id
    group by orders.salesperson_id, Number, Amount

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First write a query that will become your sub-query. It needs to return the salesperson_id and the amount of each salesperson's largest order amount.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2013
    Posts
    25

    subquery re write

    Hi PatP,
    Ok, here is my new subquery:

    (SELECT salesperson_id, MAX(Amount) AS MaxOrder
    FROM Orders
    GROUP BY salesperson_id, Amount)

    I am a newbie at this so i sure appreciate the help
    I tested the query, it returns numerous salesperson_id's and the maximum order associated with each of the references to salesperson_id's.

    What would you suggest next?

    AndrewC

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Now take that query (as a sub-query) and do an INNER JOIN against your main table on both the salesperson_id AND the transaction amount.

    There is still a potential "gotcha", but this will have you 99% complete and we'll see if your teacher was smart (or evil) enough to include data that will expose the gotcha or not.

    Oooo... I missed another "gotcha". Re-think your query a bit, you've got a problem in it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2013
    Posts
    25

    The source data

    Hi PatP,
    Well i am actually done with my class at the community college. They only went as far as intermediate sql. The problem is that in the work world the queries are more difficult than what is in school. For that reason i look online for tests and problems to improve my skills.

    I am not trying to get you to do my homework. Hehehe. Here is the actual website where the data was found:

    Advanced SQL Interview Questions and Answers Part 2

    Well here is my sql code =>

    select Orders.salesperson_id, salesperson.Name, Orders.Number, Orders.Amount
    from salesperson, Customer
    Inner join
    (SELECT salesperson_id, MAX(Amount) AS MaxOrder
    FROM Orders
    group by salesperson_id) as TopOrderAmountsPerSalesperson

    As you can see i sometimes have a problem with syntax and dont know what to do. That is currently one of my problems. But you get better with practice and that is what i am trying to do!


    AndrewC

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Look at the output of your query for the results for Dan. There should only be one "largest" value for a given sales person.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Mar 2013
    Posts
    25
    The subquery is here:
    SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
    FROM Orders
    GROUP BY salesperson_id

    From this i now get only one instance of a salesperson_id that has only the MaxOrderAmount. This is what i originally wanted.

  8. #8
    Join Date
    Mar 2013
    Posts
    25

    Output of subquery

    Here is my output of the subquery.
    I did check the data in my DB and i do have Dan for max order amount at 720 so its good. Unless i made a typo or change to the original table?

    salesperson_id MaxOrderAmount

    1 460
    2 2400
    7 720
    8 1800

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Very good! That's the problem that I caught that needed to be fixed.

    I'm trying to lead you through the process of discovery... Helping you find the answers (just like you'd have to do at work), without actually giving you those answers. This isn't quite like learning on your own, but it allows you to cover things a lot faster if you have someone experienced to give you a nudge in the right direction!

    Now that you've got the sub-query correct, you can use it with an INNER JOIN to pare down the list of orders to get the other information that the exercise requires. Give it a shot and come back with questions!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Mar 2013
    Posts
    25

    Onward again to join subquery with inner join

    Hi PatP,
    Well i hope that this dialog helps others in my situation as well?

    Here is what i have now. There are problems with it because i am lost on syntax

    Normally i do something like:
    select column1, column2
    from table A
    inner joint B
    ON tableA.id = tableB.id
    where (expression a>b or something)

    And for subqueries:
    SELECT ProductID, ProductName, InStock, OnOrder
    FROM Products
    WHERE InStock > ALL
    (SELECT InStock
    FROM Products
    WHERE InStock = 10);

    I have never combined a subquery and a inner join....help!?

    My code:

    select Orders.salesperson_id, Customer.Name, Orders.Number, Orders.Amount
    from customer
    INNER JOIN
    (SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
    FROM Orders
    GROUP BY salesperson_id)


    AndrewC

  11. #11
    Join Date
    Mar 2013
    Posts
    25

    Thank you again

    Hi PatP,
    Thank you again for your insight and help. Your help and style is really appreciated. I forgot to add that in my last post. I am really sorry. You are very insightful and a great mentor.

    AndrewC

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    select Orders.salesperson_id, Customer.Name, Orders.Number, Orders.Amount
       FROM (
          SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
             FROM Orders
             GROUP BY salesperson_id
          ) AS da_max  -- da_max alias is REALLY important in this problem
       INNER JOIN Orders
          ON <<what goes here???>>
       INNER JOIN Customers
          ON <<what goes here???>>
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Mar 2013
    Posts
    25

    Having troubles

    Hi PatP,

    Here is what i have but i am stuck again....
    I know that after ON the expression will have different tables but the columns will be the same name. For example:
    ON orders.salesperson_id = TopOrderAmountsPerSalesperson.salesperson_id


    select Orders.salesperson_id, Customer.Name, Orders.Number, Orders.Amount
    FROM (
    SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
    FROM Orders
    GROUP BY salesperson_id
    ) AS TopOrderAmountsPerSalesperson
    INNER JOIN Orders
    ON orders.salesperson_id = TopOrderAmountsPerSalesperson.salesperson_id
    INNER JOIN Customer
    ON Customer.ID = ????

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The ON clause can contain any true/false condition (this is called a Boolean expression). While you might have only seen cases where the column names were identical and compared for equality (and that is the general case), the only requirement is that it be a Boolean expression.

    Look in the columns of the Orders table for the answer to your question.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Mar 2013
    Posts
    25

    Talking out loud to share what is in my head...

    Hi PatP,
    So i see this section:

    select Orders.salesperson_id, Customer.Name, Orders.Number, Orders.Amount
    =comment = this is only using orders and customer table so it looks like i only need to join these 2 tables from what we have done so far?

    The next section of code looks like a derived table because the from is this snippet of code. I just learned about derived tables. See below:

    SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
    FROM Orders
    GROUP BY salesperson_id
    ) AS TopOrderAmountsPerSalesperson

    You have a hint directing me to the orders table and i am trying to figure out what that means exactly? and how that ties in and where?

    The derived table is pulling data from orders table. The two select statements refer to the orders table. The the confusing part is that here is an inner join again to the orders table. But i suppose in the 2nd select you can have another select with and orders table reference.

    So now if i try to fill in the 1st inner join question:

    Joins cant be joined on anything but say natural keys or foreign keys. In this case the only foreign key to use in orders table is cust_id or salesperson_id. Since we are not connecting to the customer table i wont use cust_id. So i should have:

    ON orders.salesperson_id = ??
    Now i am wondering what goes on the other side? I thought that th FROM (..)
    the code inside the from is another table. The code i am talking about is:

    (
    SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
    FROM Orders
    GROUP BY salesperson_id
    ) AS TopOrderAmountsPerSalesperson

    So this new table is, TopOrderAmountsPerSalesperson.
    So finally we have:

    ON orders.salesperson_id = TopOrderAmountsPerSalesperson.salesperson_id

    How am i doing so far?

    Than i have to handle the last bit of code:

    INNER JOIN Customer
    ON Customer.ID = ????

    Here is the customer table:

    customer
    ---------
    ID
    Name
    City
    Industry_Type

    The only foreign key is ID so that is why i used "ON Customer.ID = ??"

    I still havent figured out what to do with the "??" yet.
    The only table i have not joined is the customer table so i should have then:

    ON Customer.ID = Orders.salesperson_id

    The reason i picked orders.salesperson_id and not orders.cust_id is that i am never referencing orders.cust_id in my query but i am referencing orders.salesperson_id.

    Ok, how am i doing with my logic?

    Here is my complete code:

    select Orders.salesperson_id, Customer.Name, Orders.Number, Orders.Amount
    FROM (
    SELECT salesperson_id, MAX(Amount) AS MaxOrderAmount
    FROM Orders
    GROUP BY salesperson_id
    ) AS TopOrderAmountsPerSalesperson
    INNER JOIN Orders
    ON orders.salesperson_id = TopOrderAmountsPerSalesperson.salesperson_id
    INNER JOIN Customer
    ON Customer.ID = Orders.salesperson_id

    ps: The only problem is that this code doesnt produce the correct result but at least i showed my logic

    AndrewC

Posting Permissions

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