Virtual Tech Self-Study Message Board › Lesson 07: MySQL Marinate, Season 1

Lesson 07: MySQL Marinate, Season 1

Sheeri Kritzer C.
sheeri
Group Organizer
Boston, MA
Post #: 118
This space is reserved for updates and questions about Chapter 7 of the Learning MySQL book being used in the MySQL Marinate Season 1 virtual self-study course. If you want, you can go to the Master Discussion List for MySQL Marinate Season 1.

­

Yours in marination,

Sheeri K. Cabral
Mike M.
SeabrookMLM
Seabrook, NH
Post #: 42
Sheeri: 1) Homework Ques #6 states: "What does this tell you about using join conditions in the ON clause versus in the JOIN clause for LEFT (and RIGHT) JOINs?" Based on the two examples you included, I expected the question to be "the ON clause versus the WHERE clause".

2) Example 1 has "SELECT track_name, track.artist_id," without referring to artist_id in either an expression or the GROUP clause, which I don't think is legal. I am going to use it in an expression to get the query to fly. Please correct me if I am wrong.
Sheeri Kritzer C.
sheeri
Group Organizer
Boston, MA
Post #: 168
Mike - you are indeed correct about the typo in the first question you ask - I did mean the difference between the ON clause and the HAVING clause.

In your second point, though, it's actually NOT illegal to SELECT for something that's not in the GROUP BY or part of an expression. The queries as written do run. And in this case, since track and artist are a 1:1 relationship, grouping by track_name is sufficient. In fact, the example on the bottom of page 233 shows that this kind of query is allowed, though it usually doesn't provide the results you want.

So whole your point is incorrect, because it's legal syntax in MySQL, it's a valid point. I'll change the query.
gerv
gerv
Sheffield, GB
Post #: 5
I can't figure out question 3. Obviously, questions have to be a bit contrived, but I can't see how you could do this query as a nested query, never mind how you would want to! I've tried looking at the book to find examples of equivalent JOINs and subqueries and converting my answer to Q1 accordingly, and come up with:

SELECT artist.artist_name, album.album_name, COUNT(*) AS tracks FROM artist INNER JOIN album USING (artist_id) WHERE (artist_id, album_id) = (SELECT artist_id, album_id FROM track) GROUP BY artist_id, album_id;

but this returns "1" for all the track counts, and any attempt to modify it further produces SQL errors.

Help :-)

Gerv
Sheeri Kritzer C.
sheeri
Group Organizer
Boston, MA
Post #: 170
As a hint, try using a derived table (subquery in the FROM clause).
Patrick H.
user 74304712
Cambridge, MA
Post #: 6
Thanks for the question Gerv and the clue Sheeri. Don't think I would have got that one otherwise.

Patrick H.
user 74304712
Cambridge, MA
Post #: 7
Hi Sheeri

I am struggling to understand what the queries in question 6 are aiming to achieve.
Firstly they select different columns (the first has artist_id and the second
has album_id, which is not in the group by clause).
More importantly they are both joining (using different methods) the track and played tables
on only artist_id and album_id. Should not track_id also be included in the join?

Thanks

Patrick
Sheeri Kritzer C.
sheeri
Group Organizer
Boston, MA
Post #: 171
Hi Patrick,

You're right on the technical side of things, but this question is more of a "why do these queries give different answers?" Doing one query instead of the other is a typical mistake for someone who isn't completely familiar with the different join types, so I wanted to put an example where you have to work out why they're different. So hopefully they'll realize the difference and never make the mistake in real life (or make it but instantly realize what's happening).

I will change the queries to this:
SELECT track_name, track.album_id, COUNT(played)
FROM track
LEFT JOIN played ON (track.track_id=played.track_id
AND track.album_id=played.album_id)
WHERE track.artist_id=3
GROUP BY track_name, track.album_id;

versus

SELECT track_name, track.album_id, COUNT(played)
FROM track
LEFT JOIN played ON (track.album_id=played.album_id)
WHERE track.track_id=played.track_id
AND track.artist_id=3
GROUP BY track_name, track.album_id;
Sheeri Kritzer C.
sheeri
Group Organizer
Boston, MA
Post #: 172
Queries have been pushed up....thanx for noticing this!
Patrick H.
user 74304712
Cambridge, MA
Post #: 8
Thanks Sheeri

I understand what the question is getting at now.

Cheers

Patrick
Powered by mvnForum

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy