join problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

join problem

Author

Topic

obscuregirl
Starting Member

41 Posts

Posted - 2006-10-02 : 06:13:40

Hi

My limited SQL is struggling with this problem so any suggestions would be greatly appreciated!!

I have three tables:- Book, BookAuthor and Person. BookAuthor links Book and Person by holding the ID of a person and the ID of the book they have written. One book can have many authors.

I am trying to write a query to give me the following results:

Book1 (and some of its details) Author1, Author2, Author3

But what I'm getting is:

Book1 (and details) Author1

Book1 (and details) Author2

Book1 (and details) Author3

My query looks like this:

SELECT dbo.BookAuthor.BookID AS Publication, dbo.BookAuthor.Sequence,
dbo.Person.Surname + ' ' + dbo.Person.Initials + ',' AS Authorlist, dbo.Book.Title, dbo.Book.ISBN, dbo.Book.YearOfPublication, dbo.Publisher.PublisherName, dbo.Book.PlaceOfPublication, dbo.Book.Verified, dbo.Book.BookType
FROM dbo.Person
RIGHT OUTER JOIN dbo.Book
INNER JOIN dbo.BookAuthor ON dbo.Book.BookID = dbo.BookAuthor.BookID ON dbo.Person.PersonID = dbo.BookAuthor.PersonID
RIGHT OUTER JOIN dbo.Publisher ON dbo.Book.PublisherID = dbo.Publisher.ID
WHERE (dbo.BookAuthor.BookID IN
(SELECT BookAuthor.BookID
FROM Person
RIGHT JOIN BookAuthor ON Person.PersonID = BookAuthor.PersonID))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-02 : 07:58:43

Can you post the table structure, some sample data and the result that you want

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-02 : 08:32:22

I guess you require to use do pivot table, read on the book online under Pivot Table..

or else do as Tan' Sugguestion ;-)

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-02 : 08:57:05

There is another alternative. Search for String Concatenating function here at SQLTeam.

Peter Larsson
Helsingborg, Sweden

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-10-02 : 12:28:35

use cross tab query

suji

obscuregirl
Starting Member

41 Posts

Posted - 2006-10-03 : 08:56:00

thanks for the suggestions, I'm currently working my way through understanding them! In the meantime, here is the structure, sample data and the results that I want:

Structure:
Create Table Book
(
BookID int(4) Primary Key,
BookType nvarchar(15),
Title nvarchar(250) Not Null,
NumberOfPages smallint(2),
ISBN nvarchar(18),
YearOfPublication nvarchar(4),
PlaceOfPublication nvarchar(50),
Verified bit(1) Not Null,
PublicationType nvarchar(1),
PublisherID int(4)
)

Create Table BookAuthor
(
PersonID int(4) Primary Key,
BookID in(4) Primary Key,
Sequence tinyint(1),
RAEBookSubmission bit(1)
)

Create Table Person
(
PersonID int(4) Primary Key
Title nvarchar(4),
Surname nvarchar(50) Not Null,
Forenames nvarchar(50),
Initials nvarchar(4),
)

Create Table Publisher
(
ID int(4) Primary key,
PublisherName char(100) Not Null
)

Sample data:

Insert into Book values (1488, NULL, Nationalismen in Europa, NULL, 389-2444-79X, 2001, NULL, 0, NULL, 891)

Insert into BookAuthor values (4435, 1488, 1, 0)
Insert into BookAuthor values (4436, 1488, 2, 0)
Insert into BookAuthor values (5316, 1488, 3, 0)
Insert into BookAuthor values (5317, 1488, 4, 0)

Insert into Person values (4435, Mr, von Hirschhausen, Ulrike, U)
Insert into Person values (4436, Mr, Leonhard, Jorn, J)
Insert into Person values (5316, NULL, Goettinggen, NULL, NULL)
Insert into Person values (5317, NULL, Wallstein, NULL, NULL)

Insert into Publisher values(891, Wallstein Verlag)

Result I want:

1488, von Hirschhausen, Ulrike, U, Leonhard, Jorn, J, Goettinggen, NULL, NULL, Wallstein, NULL, NULL, Nationalismen in Europa, 389-2444-79X, 2001, Wallstein Verlag, NULL, O, NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 09:03:59

You can do that with the use of dynamic SQL, but it is a VERY VERY bad idea, since the resultset will have different number of columns each time, for any number of cowriter of book!
However, if you KNOW the number of cowriters will never be more than four, you can write a LEFT JOIN query.
But based on experience, this assumption is always wrong.

Peter Larsson
Helsingborg, Sweden

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-03 : 09:11:27

quote:
Originally posted by obscuregirl
I am trying to write a query to give me the following results:

Who will be consuming this resultset? I'd bet your front-end developer could handle an ordered book, author resultset just fine without the denormalization ...

Jay White

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-10-03 : 10:19:34

quote:
Originally posted by Page47

quote:
Originally posted by obscuregirl
I am trying to write a query to give me the following results:

Who will be consuming this resultset? I'd bet your front-end developer could handle an ordered book, author resultset just fine without the denormalization ...

Jay White

Not only can the front-end handle it fine, it is much easier for the front-end since the data comes back in a standard, predictable, consistent format without changing dynamic columns each time the stored procedure is executed. Not to mention that it is much easier for the database layer as well, since only a simple SELECT/GROUP BY is required.

Always format and present your data at the client; this includes pivotting 99% of the time. Summarize as much as you can in T-SQL, but just return the rows back to the client in a standard normalized format and let the client do the formatting.

More:

http://weblogs.sqlteam.com/jeffs/archive/2005/05/11/5101.aspx and
http://weblogs.sqlteam.com/jeffs/archive/2005/05/12/5127.aspx

- Jeff

obscuregirl
Starting Member

41 Posts

Posted - 2006-10-03 : 10:51:22

The data is going to be placed in a Crystal Report that is part of a VB.NET database front end application. The main problem is that the format of the report has to be as I described the result I wanted. I am a beginner at VB.NET and Crystal Reports and my SQL isn't that hot either so I'm a bit swamped by all this!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-03 : 10:56:41

You want to do your formatting in your Crystal Report. SQL should just be handing it data. Follow the links Jeff provided.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

obscuregirl
Starting Member

41 Posts

Posted - 2006-10-03 : 11:00:54

ok, thanks. I'll take a look at all those links. Any Crystal experts out there?! lol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources