-1

enter image description here

enter image description here

-- List all titles that have been sold along with the artist, order date and ship date

SELECT title, artist, order_date, ship_date
FROM items,orders,orderline
WHERE orders.order_id = orderline.order_id
AND items.item_id = orderline.item_id;

I tried my own query up I get results below

Under the Sun, Donald Arley, 11/15/2013, 11/20/2013

Under the Sun, Donald Arley, 12/20/2013, 12/22/2013

Under the Sun, Donald Arley, 1/18/2014, 1/23/2014

Dark Lady, Keith Morris, 1/31/2014, 2/4/2014

Dark Lady, Keith Morris, 3/10/2014, 3/15/2014

Dark Lady, Keith Morris, 3/14/2014, 3/19/2014

Dark Lady, Keith Morris, 11/15/2013, 11/20/2013

Happy Days, Andrea Reid, 2/27/2014, 3/2/2014

Happy Days, Andrea Reid, 10/30/2013, 11/3/2013

Happy Days, Andrea Reid, 12/18/2013, 12/22/2013

The Hunt, Walter  Alford, 1/31/2014, 2/4/2014

The Hunt, Walter  Alford, 3/10/2014, 3/15/2014

etc...............

Howli
  • 12,291
  • 19
  • 47
  • 72
Purple_Rain
  • 79
  • 1
  • 7
  • 1
    How are your results different than expected results? – Kevin Postlewaite Jun 04 '14 at 23:36
  • i don't think they will be, will repost tom asnwering your question – Purple_Rain Jun 05 '14 at 04:26
  • 1
    [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was discontinued with the ANSI-**92** SQL Standard (more than **20 years** ago) – marc_s Jun 05 '14 at 05:11

1 Answers1

2

This looks like a generic homework question.

I suggest you familiarize yourself with this page and site http://use-the-index-luke.com/sql/join.

Solution:

Change your statement to:

SELECT 
    items.title, items.artist, orders.order_date, orders.ship_date
FROM 
    items 
JOIN 
    orderline ON orderline.item_id = items.item_id 
JOIN 
    orders ON orders.order_id = orderline.order_id
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Carter
  • 332
  • 3
  • 13
  • I'm curious how your suggested query returns different results then the questioner's? Probably I'm missing something but I only see explicitly qualifying non-ambiguous columns and explicitly joining rather than implicitly joining, none of which should affect the data returned. – Kevin Postlewaite Jun 04 '14 at 23:35
  • @KevinPostlewaite Unless the order of table names in the FROM clause has an impact on the join, I doubt there is a difference. Hopefully Purple-Rain will clarify what is wrong with his results. – Carter Jun 04 '14 at 23:46
  • Order of columns in FROM clause has no effect on the results except for potentially the order of the results in case the database engine chooses a different execution path. – Kevin Postlewaite Jun 04 '14 at 23:49
  • On most database engines that will hold true. However, we don't know what engine he is using - most likely MySQL. But [not all engines are the same](http://stackoverflow.com/a/44981/2358135) – Carter Jun 04 '14 at 23:52
  • I dont see any change in results either, however I will get back with difference in the queries if there are any. – Purple_Rain Jun 05 '14 at 04:24