#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Posts
    2,567
    Rep Power
    171

    Alternative solution for a query


    Hello world;

    I was supposed to re-create query 1 but instead I came up with query 2. They both return the same results. I wonder if in general its ok to get the same results with another solution like this example.

    My main concern is that they SELECT FROM different tables.

    Query 1
    Code:
    SELECT customers.name                   AS customer,
           Count(items.name)                AS items,
           Sum(cartitems.qty * items.price) AS total
    FROM   customers
           INNER JOIN carts
                   ON carts.customer_id = customers.id
           INNER JOIN cartitems
                   ON cartitems.cart_id = carts.id
           INNER JOIN items
                   ON items.id = cartitems.item_id
    GROUP  BY customers.name
    My Query
    Code:
    SELECT customers.name,
           Count(cartitems.item_id)         AS items,
           Sum(items.price * cartitems.qty) AS TOTAL
    FROM   carts
           INNER JOIN cartitems
                   ON cartitems.cart_id = carts.id
           INNER JOIN items
                   ON items.id = cartitems.item_id
           INNER JOIN customers
                   ON customers.id = carts.customer_id
    GROUP  BY customers.name
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zxcvbnm
    My main concern is that they SELECT FROM different tables.
    no they don't
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Posts
    2,567
    Rep Power
    171
    Originally Posted by r937
    no they don't
    Hello Rudy;

    What I meant was one is:
    FROM customers and the other is FROM carts
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zxcvbnm
    What I meant was one is:
    FROM customers and the other is FROM carts
    yes, i understand that this is what you meant

    i assure you, the optimizer sees both FROM clauses as equivalent

    you might want to do EXPLAINs on them, for the sake of interest

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Posts
    2,567
    Rep Power
    171
    Celebrate an aha moment here. So basically all of these are the same.
    Code:
    SELECT a.id,
           b.id
    FROM   a
           INNER JOIN b
                   ON a.id = b.a_id
    Code:
    SELECT a.id,
           b.id
    FROM   b
           INNER JOIN a
                   ON a.id = b.a_id
    Code:
    SELECT a.id,
           b.id
    FROM   a
           INNER JOIN b
                   ON b.a_id = a.id
    Code:
    SELECT a.id,
           b.id
    FROM   b
           INNER JOIN a
                   ON a.id = b.a_id
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zxcvbnm
    So basically all of these are the same.
    yup

    especially as regards the ON clause, it really doesn't matter which order you write them in as far as the equality operator is concerned

    see http://en.wikipedia.org/wiki/Symmetric_relation

    basically, if A equals B, then you don't have to guess, you know that B equals A

    note, though, that not all operators share this property -- conside the "greater than" ( > ) operator

    if A > B, then you cannot say that B > A

    greater than is not symmetric, but equality is
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Posts
    2,567
    Rep Power
    171
    I am up to chapter 7. Took me 4 days but easily did all the examples and everything has been sweet so far. Saying that I am looking for a resource with more examples (specially on sub queries).

    I wait until I finish yours, I do a quick review and then I get one of these.
    Any recommendations what comes after SimplySql?

    I am getting ahead of myself here but what kind of queries have IF ELSE statements in them? What about procedures? Are they gonna come up in SimplySql?

IMN logo majestic logo threadwatch logo seochat tools logo