SQL Join Command

Filespit · June 6th, 2011, 01:11 PM

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

June 12th, 2014, 09:25 AM

Program that uses SqlCommand instance: C#

using System;
using System.Data.SqlClient;

class Program
{
static void Main()
{
//
// First, the program accesses the connection string and uses it on a connection.
//
string conString = ConsoleApplication1.Properties.Settings.Default.Co nnectionString;
using (SqlConnection connection = new SqlConnection(conString))
{
connection.Open();
//
// The SqlCommand should be created inside a using statement.
// ... It receives the SQL statement as the first argument.
// ... It receives the connection object as the second argument.
// ... The SQL text only works with a specific database.
//
using (SqlCommand command = new SqlCommand(
"SELECT TOP 3 * FROM Dogs1 ORDER BY Weight",
connection))
{
//
// Instance methods can be used on the SqlCommand instance.
// ... These read data from executing the command.
//
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine(reader.GetValue(i));
}
Console.WriteLine();
}
}
}
}
}
}

Output

7
Candy
Yorkshire Terrier

rstelma · June 12th, 2014, 04:04 PM

I think the issue is with your database design.

If it was me and I had an album with multiple producers I would create a table called Producers. Then I would have an intermediary table with an ID column, then one for AlbumID and one for ProducerID called AlbumProducers.

Then when I wanted to query info on one album I could grab all the producers at one time and not have the excessive rows for albums that is repeating the same information.

Look up "normalization" for your database design.

Imar · June 12th, 2014, 04:32 PM

Brian: what's the point of posting all this unrelated stuff that you copied from other forums? Please try not to just bluntly copy and paste other people's work without adding any value.

Cheers,

Imar

Imar · June 12th, 2014, 04:36 PM

Richard, I think the OP already has a normalized database:

Quote:

In my database I have a table called DBProducer, with the two columns ProducerID and ProducerName.

It's all the cross joins that duplicate the data. Inner joins would solve this.

But, this topic is 3 years old so I doubt the OP is still interested in an answer ;-)

Cheers,

Imar

rstelma · June 12th, 2014, 07:15 PM

Hi Imar,

Yeah.. You're right about the normalization. Just going too fast.

OK... thanks