Selecting only NOT NULL COLUMNS?

[LAYGO]

I have a table full of songs per album:

'songs' Table:
+---------+----------+-----------+-----------+-----+-----------+-------------+
| NAME | SONG01 | SONG02 | SONG03 | . . . | SONG32 | SONG33 |
+---------+----------+-----------+-----------+-----+-----------+-------------+
|ALBUM1|A1SONG1|A1SONG2|A1SONG3| . . . | NULL | NULL |
|ALBUM2|A2SONG1|A2SONG2|A2SONG3| . . . |A2SONG32| NULL |
|ALBUM3|A3SONG1|A3SONG2|A3SONG3| . . . |A3SONG32|A2SONG33|
+---------+----------+-----------+-----------+-----+-----------+-------------+

How would I select ONLY the songs that have values per row?

This makes sense, but doesn't work:

 SELECT * 
 FROM songs
 WHERE songs.* IS NOT NULL

I dont want to go thru for each field:

 WHERE song01 IS NOT NULL AND
 song02 IS NOT NULL AND
 song03 IS NOT NULL AND
. . .
 song32 IS NOT NULL AND
 song33 IS NOT NULL

Any help?

TYIA!

 

[Broccoli2]

LAYGO

Perhaps you are suffering due to the design of your tables. Here is a suggestion both to separate your table into two, and solve your problem.

First, we create the tables:

CREATE TABLE albums(
AlbumID int not null primary key auto_increment,
Album varchar(100)
);
INSERT INTO albums(Album) SELECT DISTINCT NAME FROM songs;

CREATE TABLE tracks(
TrackID int not null primary key auto_increment,
Album varchar(100),
Song varchar(100));

Make a script and cut and paste this lot - you only need to do it once...

INSERT INTO tracks(AlbumID,Song)
SELECT AlbumID, SONG00
FROM songs INNER JOIN albums ON songs.NAME=albums.Album;
INSERT INTO tracks(AlbumID,Song)
SELECT AlbumID, SONG01
FROM songs INNER JOIN albums ON songs.NAME=albums.Album;
INSERT INTO tracks(AlbumID,Song)
SELECT AlbumID, SONG02
FROM songs INNER JOIN albums ON songs.NAME=albums.Album;
...
INSERT INTO tracks(AlbumID,Song)
SELECT AlbumID, SONG33
FROM songs INNER JOIN albums ON songs.NAME=albums.Album;

Your problem is now solved with the following:

SELECT albums.Album, tracks.Track
FROM albums
INNER JOIN tracks ON albums.AlbumID=tracks.AlbumID

This method also has the benefit of being more efficient, and of having no limit on the number of tracks per album.

-Rob

 

[Broccoli2]

Oops, that last SELECT should be

SELECT albums.Album, tracks.Song
FROM albums
INNER JOIN tracks ON albums.AlbumID=tracks.AlbumID;

 

[LAYGO]

Ok, how would you handle tracks that appear on several different releases?

I already had a table similar to definition of your 'album' table. So, I'm using that as my key.

I'm relatively new to database design as you've already seen!

 

[Broccoli2]

LAYGO

What you want is a many-to-many relationship between two tables. This means each album can have many tracks, and each track can have many albums. For this we need a third 'link' table. Here's a diagram (if this looks garbled, it's probably due to the word wrap - either expand your browser or copy/paste into a text editor):

+----------------+  +----------+  +----------------+
| albums         |  | tracks   |  | songs          |
+----------------+  +----------+  +----------------+
| AlbumID*       |  | TrackID* |  | SongID*        |
| AlbumTitle     |  | AlbumID  |  | SongTitle      |
| (other fields) |  | SongID   |  | (other fields) |
+----------------+  +----------+  +----------------+

where *=primary (therefore unique) key

albums

contains all the album-specific information - the title, year, whatever. You only need one record for each album.

songs

contains all the song-specific information - the title, singers, etc. Again, you only need one record for each song.

tracks

is the table that describes how the albums are related to the songs.

Here's an example. I have AlbumA which has three tracks - SongX, SongY, and SongZ. I also have AlbumB which has four tracks - SongU, SongV, SongW, and SongX. Note that SongX is on both albums.

This would be represented in the tables like this:

albums
+---------+------------+
| AlbumID | AlbumTitle |
+---------+------------+
| 1       | AlbumA     |
| 2       | AlbumB     |
+---------+------------+

songs
+--------+-----------+
| SongID | SongTitle |
+--------+-----------+
| 1      | SongU     |
| 2      | SongV     |
| 3      | SongW     |
| 4      | SongX     |
| 5      | SongY     |
| 6      | SongZ     |
+--------+-----------+
(note that SongX does not need to be entered twice)

tracks
+---------+---------+--------+
| TrackID | AlbumID | SongID |
+---------+---------+--------+
| 1       | 1       | 4      |
| 2       | 1       | 5      |
| 3       | 1       | 6      |
| 4       | 2       | 1      |
| 5       | 2       | 2      |
| 6       | 2       | 3      |
| 7       | 2       | 4      |
+---------+---------+--------+

Now the table

songs

will hold details on all the songs in your collection (which was your original query I think).

This method may look complicated, but has some benefits:
- it makes for efficient and fast database queries
(avoiding the problems like the original one)
- it means there is no limit
on the number of songs per album
- it means there is no limit
on the number of albums per song
- you don't have to enter the same data more than once

There were a couple of typos in my first post, so here are the 'how to get there' SQL commands in full (rename your existing

songs

table to

old_songs

first:

CREATE TABLE albums(
AlbumID int not null primary key auto_increment,
AlbumTitle varchar(100));
CREATE TABLE tracks(
TrackID int not null primary key auto_increment,
AlbumID int not null,
SongID int not null);
CREATE TABLE songs(
SongID int not null primary key auto_increment,
SongTitle varchar(100));

INSERT INTO albums(AlbumTitle) SELECT NAME FROM old_songs;

Repeat the following two commands for xx from 00 to 33 (easiest to do this in a text editor, then run the file as an SQL script):

INSERT INTO songs(SongTitle)
SELECT DISTINCT SONGxx
FROM old_songs
LEFT JOIN songs ON old_songs.SONGxx=songs.SongTitle
WHERE ((songs.SongTitle Is Null) AND (old_songs.SONGxx Is Not Null));

INSERT INTO tracks(AlbumID,SongID)
SELECT albums.AlbumID, songs.SongID
FROM old_songs
INNER JOIN albums ON old_songs.NAME=albums.AlbumTitle
INNER JOIN songs ON old_songs.SONGxx=songs.SongTitle;
WHERE songs.SongID Is Not Null;

All the songs (each one listed only once) are available using:

SELECT SongTitle FROM songs;

All the albums and all their tracks are available using:

SELECT AlbumTitle, SongTitle
FROM albums
INNER JOIN tracks ON albums.AlbumID=tracks.AlbumID
INNER JOIN songs ON tracks.SongID=songs.SongID;

All this might seem a little long winded, but it really will help with querying your database in the long run.

-Rob

 

[LAYGO]

Whoa!

Thank you so much Rob for being this detailed!

Check my work here:

http://www.dethkorps.com/bhsfan/band/discography.php

Mind you, most of the releases are in there, but as as shell. The real information is not quite entered/documented except for 3 releases:

-

http://www.dethkorps.com/bhsfan/band/release.php?id=005

-

http://www.dethkorps.com/bhsfan/band/release.php?id=048

-

http://www.dethkorps.com/bhsfan/band/release.php?id=033

Also, this is all not yet implementing what you've discussed. THANKS!

 

[Broccoli2]

LAYGO

No worries. Let me know if it works...

-Rob