An sql query with join involved using Mysql

Question

enter image description here

-- 12. (trouble) List all customers, title, artist, quantity ordered

SELECT customer_name, title, artist, order_qty 
FROM customers,items,orderline, orders
WHERE  orders.order_id = orderline.order_id
  AND customers.customer_id = orders.customer_id;

I tried the query however wont get the results i need, will anyone please take a look.

results below

'Cora Blanca', 'Under the Sun', 'Donald Arley', '3'

'Cora Blanca', 'Dark Lady', 'Keith Morris', '3'

'Cora Blanca', 'Happy Days', 'Andrea Reid', '3'

'Cora Blanca', 'Top of the Mountain', 'Janice Jones', '3'

'Cora Blanca', 'Streets from Old', 'Sharon Brune', '3'

'Cora Blanca', 'The Hunt', 'Walter  Alford', '3'

'Cora Blanca', 'Rainbow Row', 'Judy Ford', '3'

'Cora Blanca', 'Skies Above', 'Alexander Wilson', '3'

'Cora Blanca', 'The Seas and Moon', 'Susan Beeler', '3'

'Cora Blanca', 'Greek Isles', 'Benjamin Caudle', '3'

Please take a look at the help centre articles on how to ask a good question. You need to include a clear explanation of the result you need, and what the problem is with what you have now. Please also tag with the type of database you are using (e.gMySQL? Postgres?) and try to apply some basic formatting to make it easier to read. You could also try creating a live demo on sqlfiddle.com — IMSoP, Jun 06 '14 at 21:09
Although the query here is different, the technique you need to understand is the same. While people here may be happy to help you understand the concepts you are struggling with, simply posting a series of very specific questions which appear to be part of a homework exercise is not a good use of this site, and will ultimately not help you learn. — IMSoP, Jun 06 '14 at 21:14
Both questions are about how to write an SQL join. You may be changing which tables you are joining and which fields you are selecting, but the _idea_ is the same. This feels like a homework question, and while it is perfectly fine to ask about how joins works, it is not ok to ask StackOverflow to solve this for you every time you need to write a join. — Chris Nielsen, Jun 06 '14 at 21:15

Matthew · Accepted Answer · 2014-06-06T21:28:41.063

1

You should not use the cartesian join that you did because it seems to be improper syntax for what you're trying to do. Specifically, you did not specify appropriate JOIN criteria for each of the tables you're trying to JOIN. You need to explicitly state their relationships.

Try this:

SELECT
    c.customer_name
    ,i.title
    ,i.artist
    ,ol.order_qty
FROM
    Customers AS c
JOIN
    Orders AS o
        ON o.Customer_Id = c.Customer_Id
JOIN
    OrderLine AS ol
        ON ol.Order_Id = o.Order_Id
JOIN
    Items AS i
        ON i.Item_Id = ol.Item_id

Rather than using a cartesian join with a WHERE clause filter you should use an explicit condition in the ON clause to define your JOIN criteria. Specially you'll notice that this solution had a condition on the items and orderline table, where your original query did not.

edited Jun 06 '14 at 21:28

answered Jun 06 '14 at 21:14

Matthew

10,244
5
49
104

I'm downvoting this because you haven't actually explained the changes you've made to the query, and which are important (hint: two of those joins are functionally identical to the original query). – IMSoP Jun 06 '14 at 21:18
i didn't down vote down but the query, doesn't seem to work. it has error on o.[customer_id" line – Purple_Rain Jun 06 '14 at 21:20
@Purple_Rain that refers to the `customer_id` column in the `orders` table... Your linked image implies it should exist. Check for typos. – Matthew Jun 06 '14 at 21:21
no the query is now pointing an error c.[customer_name]/// copied and pasted t – Purple_Rain Jun 06 '14 at 21:24
There is nothing technically wrong with using , to join tables and qualifying with WHERE clauses, and this was the only form available in early versions of SQL. The JOIN form is certainly more readable, but the only actual problem was the missing condition joining order_lines to items. – IMSoP Jun 06 '14 at 21:24
@Purple_Rain I fixed a typo for you. DO you understand what this query accomplishes (typos aside?) – Matthew Jun 06 '14 at 21:25
@IMSoP It's "wrong" because it violates the current ANSI SQL standard. I added the additional conditions **and** moved it to a corrected syntax. – Matthew Jun 06 '14 at 21:27
As for the syntax error, the square bracket syntax for quoting column names only works on MS SQL Server and maybe Oracle; MySQL uses backticks, and Postgres uses double quotes (which are the official standard). In this case, though, there's no reason to quote the names at all. – IMSoP Jun 06 '14 at 21:27
hmmm not sure it give me the same thing.. Yes a where clause condition is not used rather join... on specified condition in this one you posted... This is Mysql btw – Purple_Rain Jun 06 '14 at 21:29
@IMSoP I removed the brackets. I didn't notice the "MySQL" in the title... my mistake – Matthew Jun 06 '14 at 21:29
I'm pretty sure both forms of join syntax are in the standard. I agree JOIN + ON is better, but both will work just fine if you get the rest of the query right. The square brackets, however, definitely aren't in the standard, and will only work on a handful of database systems. – IMSoP Jun 06 '14 at 21:31
@IMSoP I disagree that both are standard. OP wants an explicit inner join, not an implicit cross join with a where clause. – Matthew Jun 06 '14 at 21:33
Even so, you still think your downvote is justified? – Matthew Jun 06 '14 at 21:34
Thanks this is SQL query i had using before. Got the same result think my foreign keys are wrong... Thanks i understand – Purple_Rain Jun 06 '14 at 21:34
@IMSoP ... for the record. I looked into the standard. The implicit inner join which the OP has used was deprecated in the SQL-92... It will still work in MySQL but it *should not be used* – Matthew Jun 06 '14 at 21:38
A restricted cartesian join is entirely equivalent to an inner join, and there is nothing about it that is non-standard. (There is actually a standard difference in binding precedence between comma-separated lists of tables and JOIN statements) And yes, I still think this answer did a poor job of explaining to someone new to SQL what was wrong with their original attempt. – IMSoP Jun 06 '14 at 21:48
The Postgres manual, which mentions the standard frequently, even when it disagrees with it, says INNER and CROSS JOIN "are just a notational convenience, since they do nothing you couldn't do with plain FROM and WHERE." http://www.postgresql.org/docs/9.3/static/sql-select.html#SQL-FROM – IMSoP Jun 06 '14 at 21:54
@IMSoP The documentation you linked explicitly states that "If multiple sources are specified, the result is the Cartesian product (cross join) of all the sources. But usually qualification conditions are added (via WHERE) to restrict the returned rows to a small subset of the Cartesian product." ... While the end result may be the same **it is incorrect** to say that an explicit inner join is the same as a subset of a cartesian product. When you scale the query in the two methods be explicit with your intent. It's possible that the query plans will not be the same even though the results are. – Matthew Jun 07 '14 at 00:31
I think we could go back and forth forever picking each other up on details. I definitely agree that using comma joins should be discouraged, and makes the OP's mistake less obvious. I disagree that they're in any way against the standard, or that the OP's query couldn't be corrected without switching syntax first, and then showing the equivalent in the better syntax. – IMSoP Jun 07 '14 at 12:17

An sql query with join involved using Mysql

1 Answers1