SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Need Help Understanding Joins

    I am not just understanding Joins there are so many and I just don't understand when to use which one. Like there is Natural Join,Outer Join, Inner Join, Join...ON, Join...Using plus alot more.

    I been reading about them and still don't understand when to use what or which one I should use for this question.

    Q5. Determine the profit of each book sold to Jake Lucas. Sort the results by the date of the order. If more than one book was ordered, have the results sorted by the profit amount in descending order.

    and I am suppost to use the Join Keywords.


    like how I understand joins is that I can only join tables together with common attributes.
    Say if table A & B are the same but you need to join table A with C but they don't have anything in common with each other but table B does you can join A with B then B with C to get what you want.

    My problem is I can't see how to join the tables together and then back to the whole thing again what join to use.

    here are the tables:



    Like the questions asks to sort the results by the date of the order. So I think I would have to Join Customers table with Orders table to get his name and have the dates he ordered his books on.

    If I understood that part right that should be no problem joining those tables together since they both share Customer# Column so they should be able to join. I am not sure what join to use but I know they can be joined.

    But the problem comes with how must profit each book was sold for since I need the Books Column which has cost & retail so I need those and minus them for each of his books he bought to find out the profit but as far as I can see they have nothing in common with each other.

    So I am just lost on what to do.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I a bit further now I found the links to join the tables together and have done that. Now I am not sure how to the next parts how to just get it to show only "Jake Lucas", how to get the profit.

    This is my code that I have

    Code:
       SELECT firstName, lastName, orderDate,cost,retail
       FROM Customers JOIN orders ON customers.customer# = orders.customer#
       JOIN orderitems ON orders.order# = orderitems.order#
       JOIN books ON orderitems.isbn = books.isbn 
       ORDER BY firstName;

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    we don't do homework assignments here, but we do give hints

    hint: for books sold to jack lucas, use a WHERE clause condition on a column from the customers table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I know you don't do ppls hw here and I don't want that might as well not attend the course then since these assignments are worth so little it won't effect my mark by much only matters on the final and midterm.


    Anways I looked up the table and found this:

    Code:
     CUSTOMER# LASTNAME   FIRSTNAME  ADDRESS              CITY         ST ZIP
    ---------- ---------- ---------- -------------------- ------------ -- -----
      REFERRED
    ----------
          1010 LUCAS      JAKE       114 EAST SAVANNAH    ATLANTA      GA 30314
    So he has a number of 1010 and thats what I want to use to identify him as since I think that would be the best since If I would just use firstname then anyone with "jake" would be chosen.

    so this is what I have:

    Code:
       SELECT firstName, lastName, orderDate,SUM(retail-cost) "Total Profit"
       FROM Customers JOIN orders ON customers.customer# = orders.customer#
       JOIN orderitems ON orders.order# = orderitems.order#
       JOIN books ON orderitems.isbn = books.isbn
       WHERE customer# = "1010";
    Yet I am getting an error now from it. I also added SUM(retail-cost) "Total Profit" to it to try to get the profit but that does not seem to be working too:

    This is the error.

    Code:
    ERROR at line 5:
    ORA-00904: "1010": invalid identifier

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    what do you think ORA-00904: "1010": invalid identifier might mean?

    oracle thinks that "1010" is an identifier, and that it's invalid

    why do you suppose it thinks that "1010" is an identifier?

    hint: what datatype is customer#?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I actually don't know what datatype customer# is. I lean more to varchar2 but it could be a number easily.

    I really don't understand what the error means I don't really know what it means by identifier.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    with double quotes around 1010, it thinks "1010" is a column name

    try it two more ways -- with single quotes around it, and then with no quotes around it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I found something similar in my book and it has no quotes so I will go with no quotes. But I tired single quotes too and both quotes/no quotes came with up with the same error so I think it might go both ways.

    I get this:

    Code:
    ERROR at line 5:
    ORA-00918: column ambiguously defined
    Like think it is saying that this column has already been defined and this is just repeating what I already done.

    I am not sure I am even close with what that error means.

    Code:
       WHERE customer# = 1010;

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you're right, it's numeric, so you can't put quotes around it

    next problem: how many of your tables have a column called customer#?

    where do you think the ambiguity might come from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well Customers table and Orders table both contain it.

    Books and orderItems do not contain it.

    So these all merged to make one table and the Customers# from Customers table and Orders table should have both overlaped. So I don't know if it does not like that because they overlaped each other or what.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, your join statement has the clause
    Code:
    ON customers.customer# = orders.customer#
    so obviously, whatever value one of them is, the other one has to be the same value, right?

    so what could be ambiguous about the where clause?
    Code:
    WHERE customer# = 1010;
    admittedly, the database could apply the WHERE condition to either column, and get the same result, because they have to be the same value

    unfortunately, it doesn't work like that

    you've referred to a column in the WHERE clause which the database doesn't know which table it comes from

    it's the same ambiguous syntax problem it would have if your join condition was
    Code:
    ON customer# = customer#
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So your saying I have to point it too which table I want it to go to?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yep

    and i would suggest that it be WHERE Customers.customer# = 1010
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok So now I get a new error(at this rate I think I will get every error possible)

    I get this:
    Code:
    ERROR at line 1:
    ORA-00937: not a single-group group function
    So I tried to do this:
    Code:
    SELECT firstName, lastName, orderDate, SUM(retail-cost) "Total Profit"
       FROM Customers JOIN orders ON customers.customer# = orders.customer#
       JOIN orderitems ON orders.order# = orderitems.order#
       JOIN books ON orderitems.isbn = books.isbn
       WHERE Customers.customer# = 1010 
       GROUP BY firstName, lastName, OrderDate, SUM(retail-cost) "Total Profit";
    now I get this
    Code:
    GROUP BY firstName, lastName, OrderDate, SUM(retail-cost) "Total Profit"
                                                *
    ERROR at line 6:
    ORA-00934: group function is not allowed here

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    "group function is not allowed here" means you cannot have an aggregate expression (like SUM) in the GROUP BY clause

    listen, i'd love to tutor you some more, but i'm going to bed

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok thxs for you help.

  17. #17
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So where have you got with this, chobo2?

    Tryst


Bookmarks

Posting Permissions

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