Hi!
Not sure if I've posted this one in the correct topic, so feel free to move it. Anyway, here goes.
I am building a music website where I want to display albums. I have built a database that works fine, but I have a problem with the SQL command.
In my database I have a table called DBProducer, with the two columns ProducerID and ProducerName. I have inserted two values this far (see list below). I am using the following SQL command.
Code:
SELECT DBAlbum.AlbumName, DBArtist.ArtistName, DBLabel.LabelName, DBGenre.GenreName, DBProducer.ProducerName, DBGrade.Grade
FROM DBAlbum CROSS JOIN DBGrade CROSS JOIN DBProducer CROSS JOIN DBArtist CROSS JOIN DBGenre CROSS JOIN DBLabel
WHERE (DBAlbum.AlbumID = 1) AND (DBGrade.GradeID = 5)
With this I display the following:
Code:
AlbumName ArtistName LabelName GenreName ProducerName Grade
Album 1 Artist 1 Label 1 Punk John 5
Album 1 Artist 1 Label 1 Punk Adam 5
On my website I have a DataList that displays this. By adding "(DBProducer.ProducerID = 1)" to the WHERE command, I only display the first line (John). If I don't add this, the site displays all the information twice, with John as producer in the first set and Adam as the producer in the second. What I want to do is the DataList to display both John and Adam together in one set, and not display the same information twice with different producers, so that in my aspx file the code should look something like this:
Code:
<asp:Label ID="ProducerNameLabel" runat="server" Text='<%# Eval("ProducerName") %>' />
&
<asp:Label ID="ProducerNameLabel" runat="server" Text='<%# Eval("ProducerName") %>' />
(The first label shall display the name John and the second label shall display the name Adam)
How do I do that? I suppose I need a JOIN command but I can't figure it out.
Help would be much appreciated!
//Peter