Making selections across related tables and displaying the content

[brianbehrens]

I don't use this as much as I should, and when I do I really seem to struggle with it.

 

I'm creating a simple database for a band.  I have a discography section where I want to display the albums from 1 table and the related songs from another.

 

The ALBUMS table has the following fields

id (primary key)

title

year

 

and the ALBUM_SONGS has the following fields

id (primary key)

album_id

track_number

title

 

How do I pull out each album and the related songs to that album in 1 SQL query?

 

I know it can be done, but like I said, I don't do this that often and my mySQL skills are poor.

 

Also not knowing exactly how the array comes back, any help with displaying the information in PHP would be a big help. 

 

Really where I struggle is the MySQL though.  This forum has been a great resource, and I thank everyone for the continued help / support.

[teng84]

please have at least the basic before asking read cooollll

http://w3schools.com/php/php_mysql_connect.asp

[brianbehrens]

No where in there do they reference SQL Queries across 2 tables.  Thanks for all your help buddy. 

[teng84]

http://w3schools.com/sql/sql_join.asp  ;D ;D very basic and coolllll

[brianbehrens]

ahh yes.

 

SELECT albums.title, albums.date, albums.image_type, album_songs.id, album_songs.album_id, album_songs.song_number, album_songs.song_title FROM albums, album_songs WHERE albums.id=album_songs.album_id ORDER BY albums.date DESC, album_songs.song_number ASC

 

good stuff.

 

I need to brush up on my MySQL.  Thanks for the resource, but I thought it would be a little harder than that.  I guess not.  A little reading never hurt anyone.

[keeB]

Also take a look at:

 

SELECT * from albums a

inner joins album_songs as on a.id = as.album_id

 

Much less complicated than your long SQL

[brianbehrens]

So by the statement "albums a"... you are setting the albums table to an alias of a?

 

in reference to:

SELECT * from albums a

inner joins album_songs as on a.id = as.album_id

 

[keeB]

Yep

[brianbehrens]

I noticed by using "a" and "as" as aliases, it cause sql syntax errors.  So I switched them up.

 

SELECT * from albums al INNER JOIN album_songs als ON al.id = als.album_id ORDER BY al.date DESC, als.song_number ASC";

 

thank you.

[keeB]

yeah 'as' is a reserved keyword -- didn't think about that.

 

Glad you got it workin

[brianbehrens]

This is working great, but I'm now running into an issue where if there are no songs available for listing on the album, the album itself will not display because there are no associated songs.  Is there a way to display the albums with no associated songs in the database without an additional query?

 

Seems like I would do a LEFT JOIN, but that extra album with no matching songs is not displaying.

[brianbehrens]

Ahh yes, that does work, the left join that is.

 

It was more an error in my MySQL Table configuration.  I need to be sure not to use the same name for fields in relational databases.

 

Learning... 

[keeB]

w2g for figuring it out. Never used LEFT JOIN myself (I suppose I've never needed to??) but understand what it does in your context.

[brianbehrens]

Yeah, I ran into problems using fields with the same name in relational database such as "id"... need to start making that more unique across multiple relational databases... just makes it easier.