我可以编写什么sql语句来获取专辑的所有链接轨道

Question

I'm trying to think of an SQL statement I can write up in MySQL in which I can get all the tracks included inside an album. The way I have it set up is Album_id which is the primary key to my album table is linked inside the tracks table in the form of a foreign key. That way each track row is linked to an album in the album table.

I'll further detail below the layout of my tables:

tbl_Albums

As you can see I have the Album_id as the primary key and it's auto_incremented so the id will automatically update when a new row is entered.

CREATE TABLE `tbl_Albums` (
`Album_id` int(11) NOT NULL auto_increment,
`Album_Name` varchar(32) NOT NULL,
`Number_Of_Tracks` int(11) NOT NULL,
`Genre` varchar(32) NOT NULL,
`Artist_id` int(11) NOT NULL,
 PRIMARY KEY  (`Album_id`),
 KEY `Artist_id` (`Artist_id`),
 CONSTRAINT `tbl_Albums_ibfk_2` 
 FOREIGN KEY (`Artist_id`) REFERENCES `tbl_Artist` (`Artist_id`))

tbl_Tracks

Inside the tracks table I set a foreign key with album_id, so I link the tracks to a specific album.

CREATE TABLE `tbl_Tracks` (
`Track_id` int(11) NOT NULL auto_increment,
`Track_Name` varchar(32) NOT NULL,
`Length` time NOT NULL,
`Artist_id` int(11) NOT NULL,
`Album_id` int(11) NOT NULL,
 PRIMARY KEY  (`Track_id`),
 KEY `Artist_id` (`Artist_id`),
 KEY `Album_id` (`Album_id`),
 CONSTRAINT `tbl_Tracks_ibfk_1` 
 FOREIGN KEY (`Artist_id`) REFERENCES `tbl_Artist` (`Artist_id`),
 CONSTRAINT `tbl_Tracks_ibfk_2` 
 FOREIGN KEY (`Album_id`) REFERENCES `tbl_Albums` (`Album_id`))

I have the tables linked properly, but I'm trying to think of an MySQL statement that can allow me to show all the tracks linked to a specific album and have come up empty so far. I'm then trying to implement the code into a php form for a user to use to list out the tracks

Any help would be greatly appreciated.

Answer 1

    SELECT
      `t2`.*
    FROM
      `tbl_Albums` `t1`
    INNER JOIN
      `tbl_Tracks` `t2`
    ON
      `t2`.`Album_id`=`t1`.`Album_id`
    WHERE
      `t1`.`Album_Name`='Name of Your album'
    ORDER BY
       `t2`.`Track_id`;