dizzy in the head with this query

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 2008 Forums

Transact-SQL (2008)

dizzy in the head with this query

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-01-11 : 17:47:09

Hi there

I have the following scenario. There can be many to many relationships between authors and books.

1. Authors can be the exclusive primary authors on a book with no one else collaborating
2. Authors could collaborate on same book with another author. Others authors are not primary
3. All authors could be primary authors on same book


declare @authors table(author_id int, author_name nvarchar(50))
declare @books table(book_id int,  book_title nvarchar(50))
declare @authors_books table(authors_books_id int, author_id int, book_id int, primary_author bit)

insert into @authors
Select 1, 'A'
UNION
Select 2, 'b'
UNION
Select 3, 'c'
UNION
Select 4, 'd'
UNION
Select 5, 'r'


insert into @books
Select 1, 'A book'
UNION
Select 2, 'b book'
UNION
Select 3, 'c book'
UNION
Select 4, 'd book'
UNION
Select 5, 'r book'


insert into @authors_books
Select 1, 1, 1, 1
UNION
Select 2, 2, 1, 0
UNION
Select 3, 3, 1, 0
UNION
Select 4, 1, 2, 1
UNION
Select 5, 1, 2, 1
UNION
Select 6, 3, 3, 0
UNION
Select 7, 4, 3, 0


select * from @authors
select * from @books
select * from @authors_books

Is having primary bit field on @authors_books not a good design approach?

Thanks

If you don't have the passion to help people, you have no passion

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-01-11 : 17:54:26

Design approach looks good to me.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-11 : 17:57:38

Until someone asks him to sum up the primary authors or do any kind of "how many" queries. I don't know about database design, so I'll defer, but I do know that it's much easier to write
select sum(primary_author) instead of
select sum(case when primary_author = 1 then 1 else 0 end)

Jim

Everyday I learn something that somebody else already knew

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-01-11 : 18:19:36

thanks Vinnie. How would I go about so I can get one row result no matter I have one primary author(s) or many or none and concatenate the author(s)

If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-11 : 18:32:31

I don't know your business rules, but what is the diference between no primary authors and all primary authors?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-01-11 : 18:48:53

No primary author and all primary authors means it is the same just that the data is sparse for now

If you don't have the passion to help people, you have no passion

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-11 : 19:29:33

I have the following scenario. There can be many to many relationships between authors and books.

1. Authors can be the exclusive primary authors on a book with no one else collaborating
2. Authors could collaborate on same book with another author. Others authors are not primary
3. All authors could be primary authors on same book

Let's use better DDL with keys and DRI. Also, the industry standard for books is called the ISBN. T-SQL has proper row constructots now, so you do not have to use that UNION stuff.

CREATE TABLE Authors
(author_id INTEGER NOT NULL PRIMARY KEY,
author_name NVARCHAR(50) NOT NULL);

CREATE TABLE books table
(isbn CHAR(13) NOT NULL PRIMARY KEY,
book_title NVARCHAR(50) NOT NULL);

>> Is having primary BIT field [sic: columns are not fields] on Authorship not a good design approach? <<

Do not use BITs in SQL; I just wrote a whole article on this. Your authorship table was wrong. You have a proper key, so why create an absurd extra column?

CREATE TABLE Authorship
(author_id INTEGER NOT NULL
REFERENCES authors (author_id)
ON UPDATE CASCADE),
isbn CHAR(13) NOT NULL
REFERENCES Books(isbn),
ON UPDATE CASCADE),
authorship_seq INTEGER DEFAULT 1 NOT NULL
CHECK (authorship_seq > 0),
PRIMARY KEY (author_id, isbn, authorship_seq));

Now to find the primary author (i.e. the guy with the minimum authorship_seq)

SELECT A1.isbn, A1.author_id
FROM Authorship AS A1
WHERE A1.authorship_seq
= (SELECT MIN (A2.authorship_seq)
FROM Authorship AS A2
WHERE A1.isbn = A2.isbn);

Your mindset has not made the jump to thinking in sets yet.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-01-11 : 19:40:31

Celko thanks for opening the worm hole for my mindset!

Thank you very much

If you don't have the passion to help people, you have no passion

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-01-11 : 21:57:41

I am not certain if I am following you... Is this all you're looking for?

Obviously this is test data and in the live data you would use different data identifiers and add any restraints that fit, but for sample purposes are you looking for more than just this?




declare @authors table(author_id int, author_name nvarchar(50))
declare @books table(book_id int,  book_title nvarchar(50))
declare @authors_books table(authors_books_id int, author_id int, book_id int, primary_author bit)

insert into @authors
Select 1, 'A'
UNION
Select 2, 'b'
UNION
Select 3, 'c'
UNION
Select 4, 'd'
UNION
Select 5, 'r'


insert into @books
Select 1, 'A book'
UNION
Select 2, 'b book'
UNION
Select 3, 'c book'
UNION
Select 4, 'd book'
UNION
Select 5, 'r book'


insert into @authors_books
Select 1, 1, 1, 1
UNION
Select 2, 2, 1, 0
UNION
Select 3, 3, 1, 0
UNION
Select 4, 1, 2, 1
UNION
Select 5, 1, 2, 1
UNION
Select 6, 3, 3, 0
UNION
Select 7, 4, 3, 0

SELECT s1.book_id,book_title ,
		STUFF((SELECT TOP 100 PERCENT ',' + case when s2.primary_author = 1 then ' Primary: ' + b.author_name else ' Collaborator: ' + b.author_name  end FROM @authors_books s2 inner join @authors b on s2.author_id = b.author_id  WHERE s2.book_id  = s1.book_id  ORDER BY ',' + b.author_name  FOR XML PATH('')), 1, 1, '') AS Authors
FROM		@Books AS s1
ORDER BY	s1.book_id

Also PESO is responsible for that concat trick using for xml.

Also, I do not think your table structure is wrong. In my opinion there is no absurd extra column, since you want to keep books separated and authors separated. I always find it benefitial to have have a id column even if it is not a primary.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-01-11 : 22:13:55

I included in this sample the "AUTH_SEQ" field so you can use that in conjunction with the primary if you wanted. Hopfully this assists you in finding your solution.




declare @authors table(author_id int, author_name nvarchar(50))
declare @books table(book_id int,  book_title nvarchar(50))
declare @authors_books table(authors_books_id int, author_id int, book_id int, primary_author bit,auth_seq int)

insert into @authors
Select 1, 'A'
UNION
Select 2, 'b'
UNION
Select 3, 'c'
UNION
Select 4, 'd'
UNION
Select 5, 'r'


insert into @books
Select 1, 'A book'
UNION
Select 2, 'b book'
UNION
Select 3, 'c book'
UNION
Select 4, 'd book'
UNION
Select 5, 'r book'


insert into @authors_books
Select 1, 1, 1, 1,1
UNION
Select 2, 2, 1, 0,2
UNION
Select 3, 3, 1, 0,3
UNION
Select 4, 1, 2, 1,2
UNION
Select 5, 1, 2, 1,1
UNION
Select 6, 3, 3, 0,2
UNION
Select 7, 4, 3, 0,1

SELECT	s1.book_id,book_title ,
		STUFF((SELECT TOP 100 PERCENT ',' + case when s2.primary_author = 1 then ' Primary: ' + b.author_name else ' Collaborators: ' + b.author_name  end FROM @authors_books s2 inner join @authors b on s2.author_id = b.author_id  WHERE s2.book_id  = s1.book_id  ORDER BY  s2.primary_author desc, s2.auth_seq  FOR XML PATH('')), 1, 1, '') AS Authors
FROM		@Books AS s1
ORDER BY	s1.book_id

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-01-11 : 23:39:55

Thanks Vinnie

this looks awesome and works perfectly!!! When you are staring at something for too long, and you got stuff piled up, can't see clearly

If you don't have the passion to help people, you have no passion

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 00:17:43

Is it possible that a book may NOT have a primary author ?

PBUH

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-01-12 : 00:58:19

Sachin

yes it is possible that a book may NOT have a primary author .

Thank you all

If you don't have the passion to help people, you have no passion

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 02:13:17

Well what I would do is create one more table say name PrimaryAuthors with BookId and AuthorId and insert only those records for books who have primaryautor with the corresponding AuthorId and BookId and remove the field primary_author from the authors_books table.

So maybe this way in future if no of records are huge in authors_books then you dont need to query the authors_books to find the authors who are primary authors or the books who have primary author.

Like say book A has 15 authors with just 1 as primary author.So instead of querying the authors_books table to find one row in 15 rows I can query PrimaryAuthors table to find the primary author for book A.

I myself am not that sure on this design.Maybe other SQL pundits can give their feedback on this.

PBUH

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-12 : 11:07:08

quote:
Originally posted by jcelko

I have the following scenario. There can be many to many relationships between authors and books.

1. Authors can be the exclusive primary authors on a book with no one else collaborating
2. Authors could collaborate on same book with another author. Others authors are not primary
3. All authors could be primary authors on same book

Let's use better DDL with keys and DRI. Also, the industry standard for books is called the ISBN. T-SQL has proper row constructots now, so you do not have to use that UNION stuff.

CREATE TABLE Authors
(author_id INTEGER NOT NULL PRIMARY KEY,
author_name NVARCHAR(50) NOT NULL);

CREATE TABLE books table
(isbn CHAR(13) NOT NULL PRIMARY KEY,
book_title NVARCHAR(50) NOT NULL);

>> Is having primary BIT field [sic: columns are not fields] on Authorship not a good design approach? <<

Do not use BITs in SQL; I just wrote a whole article on this. Your authorship table was wrong. You have a proper key, so why create an absurd extra column?

CREATE TABLE Authorship
(author_id INTEGER NOT NULL
REFERENCES authors (author_id)
ON UPDATE CASCADE),
isbn CHAR(13) NOT NULL
REFERENCES Books(isbn),
ON UPDATE CASCADE),
authorship_seq INTEGER DEFAULT 1 NOT NULL
CHECK (authorship_seq > 0),
PRIMARY KEY (author_id, isbn, authorship_seq));

Now to find the primary author (i.e. the guy with the minimum authorship_seq)

SELECT A1.isbn, A1.author_id
FROM Authorship AS A1
WHERE A1.authorship_seq
= (SELECT MIN (A2.authorship_seq)
FROM Authorship AS A2
WHERE A1.isbn = A2.isbn);

Your mindset has not made the jump to thinking in sets yet.

--CELKO--

Joe, everything you said is pretty spot on. However, your solution doesn't satisfy one of the requirements (as I understand them). There is no way to tell when a book as mutiple authors if there is *not* a primary author. I think you built in an assumption that there is always a primary author. Maybe that's how it is in the real world, but I tend to doubt that.

What if there are 5 authors and 2 are primary? That *seems* like a valid scenario.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-01-12 : 18:17:48

you are right there could be 5 authors and 2 are primary. the rest could have written on a specific part of the book that deals with their specialty? feasible

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources