creating temp table from two other tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

creating temp table from two other tables

Author

Topic

Z.K.
Starting Member

5 Posts

Posted - 2009-10-14 : 05:50:46

I have a bit of a problem. I have one table that has a list of titles and another table that has a list of authors for that title and a title_id. Some titles may have more than one author so the title would be listed more than once in the names table. I would like to somehow combine the two tables, but I am unsure just exactly how to do this.

Below is what I have:

names table:

1 1 Robin Dewson Beginning SQL Server 2005 Express for Developers
3 2 Jesse Liberty Programming C#
4 3 Alexandre Lobao Beginning XNA 2.0 Game Programming
5 3 Bruno Evangelista Beginning XNA 2.0 Game Programming
6 3 Jose Antonio Beginning XNA 2.0 Game Programming
7 3 Leal de Farias Beginning XNA 2.0 Game Programming
8 4 Elizabeth Castro HTML, XHTML & CSS
9 5 Charles Petzold Programming Microsoft Windows With C#
10 6 David Makofske TCP/IP Sockets In C#
11 6 Michael Donahoo TCP/IP Sockets In C#
12 6 Kenneth Calvert TCP/IP Sockets In C#

Titles table:

1 Beginning SQL Server 2005 Express for Developers Programming 436 39.99
2 Programming C# Programming 644 44.95
3 Beginning XNA 2.0 Game Programming Programming 429 39.99
4 HTML, XHTML & CSS Programming 456 34.99
5 Programming Microsoft Windows With C# Programming 1290 59.99
6 TCP/IP Sockets In C# Programming 175 26.95

How I would like it to look:

Titles/Authors table or View:
1 Robin Dewson Beginning SQL Server 2005 Express for Developers Programming 436 39.99
2 Jesse Liberty Programming C# Programming 644 44.95
3 Alexandre Lobao, Bruno Evangelista, Jose Antonio, Leal de Farias Beginning XNA 2.0 Game Programming Programming 429 39.99
4 Elizabeth Castro HTML, XHTML & CSS Programming 456 34.99

I was able to get this code to work to combine the authors into a single string.

DECLARE @names nvarchar(max)

SELECT @names = ISNULL(@names + ', ','')+ first_name+' '+ last_name
FROM name
WHERE name.title_id = 3

print @names

But I am unsure how to use it as a subquery when creating a new table. When I try, I get an sql error about returning more than one value.

I can get this to work at the web server level, but I was kind of hoping I could combine the two tables into a view to make things a bit easier. Any suggestions would be welcome.

Z.K.

asgast
Posting Yak Master

149 Posts

Posted - 2009-10-14 : 08:42:13

lol :)
if you want to combine tables, you should use join
if you were able to come up with the code to combine the authors then writing a join should be nothing for you

P.S. Sometimes you meet the same people in different places on the web

Why can't you ask me on the same forum, where I wrote you this script for y?ou

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

Z.K.
Starting Member

5 Posts

Posted - 2009-10-14 : 13:51:28

Actually, I was going to, but I misplaced my login temporarily. I will see if I can find it today or have the information emailed to me.

If you have not noticed, I am a bit new to creating sql statements that work for me. Yes, I tried creating a join, but I could not get it to work just right. Oh well, I guess I will just use C# and asp.net to do what I need to. Thanks anyway.

Z.K.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources