To simplify your joins you may want to consider using the WHERE a.pk = b.fk
syntax. It's often much clearer than the oft convoluted JOIN ... ON
-- I took the liberty of using table aliases as well
SELECT a.name, b.title, s.title, b.year
FROM artist a, album b, song s
WHERE a.id = b.artist_id
AND b.id = s.album_id
ORDER BY a.name, b.year, b.title, s.title
In some databases this can lead to much faster queries as well. In these cases, by not explicitly stating the join order, the query optimiser can step in and decide based on indices and other factors.
Another thing of note is that as of 3.23.44 MySQL does support foreign key relation(finally!) a bit in the InnoDB table type. If you are using MySQL as a relational database I urge, no implore, you to update and use said table types. Without such contraints (and fully functional transaction, but we'll ignore that for now) you're really not even using a relational database. As an added incentive they'll vastly improve the robustness of your application and remove a lot of (what should be) uneccassy work at the application level.
(And yes I do froth at the mouth a bit when expounding on the evils of bad, or the lack of, data-integrity checks ;)
To create an InnoDB table in MySQL simply append 'TYPE=INNODB' to all your CREATE TABLE statements.
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
for more details on the syntax and restrictions.
Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
Read Where should I post X? if you're not absolutely sure you're posting in the right place.
Please read these before you post! —
Posts may use any of the Perl Monks Approved HTML tags:
You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
- a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
Link using PerlMonks shortcuts! What shortcuts can I use for linking?
See Writeup Formatting Tips and other pages linked from there for more info.
| & || & |
| < || < |
| > || > |
| [ || [ |
| ] || ] ||