2

I'm having a problem sorting my tables in MYSQL.

I have my tables setup like this:

Order_details

  • Order_ID
  • shipping_cost
  • printed

Product_details

  • ID
  • Order_ID
  • SKU_location

I want to SELECT all orders WHERE printed = FALSE but also sort by the shipping costs and THEN by the SKU_location

How can I join the tables into one query so it sorts by shipping_cost and SKU_location where printed = false?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Stu
  • 23
  • 1
  • 3

1 Answers1

4

You can do an implicit JOIN in the following way:

SELECT * 
FROM Order_details od
JOIN Product_details pd
ON od.Order_ID = pd.Order_ID
WHERE od.printed = FALSE
ORDER BY od.shipping_cost, pd.SKU_location

The text following each table renames the table for easy reference in the later parts of the query (i.e. the code "Database.Long_Table_Name ltn" renames the table to "ltn")

Jeff Ferland
  • 17,832
  • 7
  • 46
  • 76
Evan Bowling
  • 53
  • 3
  • 2
    No need to do an implicit join... SELECT * FROM Order_details od INNER JOIN Product_details pd ON od.Order_ID = pd.Order_ID WHERE od.printed = FALSE ORDER BY od.shipping_cost, pd.SKU_location – Jody Feb 21 '11 at 16:56
  • Seconding Jody's comment. Please don't perpetuate the use of implicit joins. – Joe Stefanelli Feb 21 '11 at 17:01
  • @Jody, @Joe : is your suggestion based on the readability of the query? I can see how it is more clear that an expensive operation(join) is being performed by the query that Jody posted. – Evan Bowling Feb 21 '11 at 17:11
  • I'd say it's debatable that a join is more expensive than a where by default, taking into consideration indexes etc. For me using explicit joins makes it easier to see what is part of a join and what is part of the actual where clause. In some cases you can have a join on 2 fields, which in an implicit join is quite easy to miss. Also, implicit OUTER joins have been deprecated in MSSQL 2008. There's a topic on it here - http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins – Jody Feb 21 '11 at 17:16