Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > Visual Web Developer 2008
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Visual Web Developer 2008 Discuss creating ASP.NET 3.5 sites with Microsoft's Visual Web Developer 2008. If your question is more specific to a piece of code than the Visual tool, see the ASP.NET 3.5 forums instead. If your question is specific to the "Express Edition" be sure to state that in your post.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Visual Web Developer 2008 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 6th, 2011, 02:11 PM
Authorized User
Points: 94, Level: 1
Points: 94, Level: 1 Points: 94, Level: 1 Points: 94, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2010
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Join Command - Merge 2 Columns

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
Reply With Quote
  #2 (permalink)  
Old June 12th, 2014, 10:25 AM
Banned
Points: 94, Level: 1
Points: 94, Level: 1 Points: 94, Level: 1 Points: 94, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2014
Posts: 20
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old June 12th, 2014, 05:04 PM
Friend of Wrox
Points: 2,077, Level: 18
Points: 2,077, Level: 18 Points: 2,077, Level: 18 Points: 2,077, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 594
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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.
Reply With Quote
  #4 (permalink)  
Old June 12th, 2014, 05:32 PM
Imar's Avatar
Wrox Author
Points: 67,581, Level: 100
Points: 67,581, Level: 100 Points: 67,581, Level: 100 Points: 67,581, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,269
Thanks: 66
Thanked 1,428 Times in 1,408 Posts
Default

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 Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #5 (permalink)  
Old June 12th, 2014, 05:36 PM
Imar's Avatar
Wrox Author
Points: 67,581, Level: 100
Points: 67,581, Level: 100 Points: 67,581, Level: 100 Points: 67,581, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,269
Thanks: 66
Thanked 1,428 Times in 1,408 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #6 (permalink)  
Old June 12th, 2014, 08:15 PM
Friend of Wrox
Points: 2,077, Level: 18
Points: 2,077, Level: 18 Points: 2,077, Level: 18 Points: 2,077, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 594
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Hi Imar,

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

OK... thanks
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
T-SQL MERGE and OPENQUERY 4thhorseman SQL Server 2008 1 December 3rd, 2009 09:42 AM
Merge Command uma_10_5 Oracle 3 January 11th, 2006 02:37 AM
Merge/Append Columns calamillo Access 3 November 30th, 2005 04:51 PM
SQL Join mattastic SQL Server 2000 9 April 21st, 2005 11:27 AM
Outer Join, 2 columns jking SQL Language 1 December 5th, 2004 04:14 AM



All times are GMT -4. The time now is 02:34 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.