[RESOLVED] Retrieving three author names, two may be empty

[srisa]

Hello,
I have a books table which apart from other fields has fields author1, author2, author3 which refer to the id in authors table. author2 and author3 may be 0, which means the book has only 1 author. Issue is how to retrieve author names for author2 and author3 only if they are not 0?
First retrieving the author1, author2, author3 id's and then retrieving their names if they are not 0 is something I can do from php.
I wanted to know if it can be done in a single sql statement. Or is there a better way of organizing the data?
I am using MySQL 5.0
Thank you.

[RhinoBull]

It appears to be a very strange design...
I think it would be better if you only have ONE AuthorID in your Books table.
The combination of BookID+AutorID is unique so you can easily link Books and Authors tables on AuthorID field.

[srisa]

I couldn't understand the third line. I didn't mention that a book can have more than one author and an author can have more than one book. That being the case how can I have more than one author for a single book with just one authorid field. I do not have any entries for the bookid in the authors table.

[GaryMazzone]

The author field would not be in the book table. You would have a book table and an Authors table. There would be another table AuthorToBook that would hold the Primary Keys from each table for each each auther to a book. This way a author is only entered into the system once. You can have more then one auther per book and an author can be assigned to more then one book. This is known as a Many-To-Many relationship

[srisa]

And AuthorToBook will be something like this
----------------------------------
id - primary key
bookid - references book id
authorid - references author id
----------------------------------
If a book has more than one author it will have more than one entry in this table.
Correct me if I am wrong.

[GaryMazzone]

That is correct. There will always be one combination for every book but there can be many.

[szlamany]

Just to argue the other side of this

If you were to look at all the books you were going to load and found that 97% of them had only one author. And maybe 2% of the remaining had 2 authors and that only 1% ever had 3 authors...

Then this would be a place were you could consider ignoring 3rd normal form.

I'm certainly not saying this is the place to ignore the rule...

Just stating what I sometimes look at before deciding it's time to make a child table and increasing the complexity of joins...

If you always want the 1, 2 or 3 authors on the same row as the book then it's going to be much harder to do that with a query.

But the other argument to use 3rd normal form is that you will not be able to easily find all books written by "author xyz" if you have 3 fields in the book table.

As to your original question it's very easy to get the author names based on your original design

Select BookName,A1.AuthorName,A2.AuthorName,A3.AuthorName From Books BK
 Left Join Authors A1 on A1.AuthorId=BK.AuthorId1
 Left Join Authors A2 on A2.AuthorId=BK.AuthorId2
 Left Join Authors A3 on A3.AuthorId=BK.AuthorId3

If the BK.Author# field is a 0 it won't find a relationship in the Authors table - thus returning a NULL name.

________________________________________________________________

And AuthorToBook will be something like this
----------------------------------
id - primary key
bookid - references book id
authorid - references author id
----------------------------------
If a book has more than one author it will have more than one entry in this table.
Correct me if I am wrong.

If you go this route there is no need for an ID field - the BOOKID and AUTHORID are two fields that can be made into a "composite primary key". This works fine - and has the added benefit of forcing only one row per book/author combination to ever be allowed.

There is no benefit to the ID field - it doesn't supply a purpose in my opinion.

[RhinoBull]

As to your original question it's very easy to get the author names based on your original design

But it's bad and I don't know if it can get any worst...

... the BOOKID and AUTHORID are two fields that can be made into a "composite primary key". This works fine - and has the added benefit of forcing only one row per book/author combination to ever be allowed...

We already mentioned that but in fewer words of course.

[szlamany]

We already mentioned that but in fewer words of course.

But those fewer words weren't being listened to...

[srisa]

I learned a lot from this. Hopefully I won't forget it. The id field is sort of thumb rule for me, it does the job most of the time, but now I realize it is not always so.
Thanks to all of you.

[Ken B]

You really don't need to have an id field. If you make the authorBook table primary key both bookid and authorid .

bookid - references book id
authorid - references author id