Miscrosoft Access 2007 SQL Query

[irenavassilia]

Hi I have three tables in my database:

table1 - Library

Columns and current data for table Library:

ISBN BookName PageCount Borrowed Rating CopyrightDate
014042430X Complete Writings 650 Y 5 11/27/1999
032121353X Visual Quickstart Gu 1239 Y 1 01/01/1999
080712320X Running A Thousand M 830 N 1 02/23/2000
1111111111 JavaBooks
130082167 Cost Accounting 539 N 3 10/23/2000
130082171 Management Accountin 388 N 1 12/06/2000
131430211 Financial Reporting 232 N 3 08/04/2005
131475912 Accounting Informati 1810 N 3 08/20/2002
131475916 Accounting Informati 2047 N 5 12/18/2002
131495380 Cost Accounting 2521 N 4 08/15/2003

table2 - Authors

Columns and data for Authors table:

First Name Last Name ISBN
Weinman Braun 032121353X
Weinman Braun 321199553
Hopkins Paige
Blazek Paige 471412856
Carson Yarbrough 9999999999
Walker Yarbrough
Robinson Yarbrough 870495275

table3 - Publishers

Columns and data for Publishers table:

PubID PublisherName Rating ISBN
1 Ace Books 1 032121353X
2 Academic P 3 321199553
3 Addison-We 4 9999999999
50 2
51 Sheridan 2222222222
52 Sheridan 1111111111

As you can see all three table hold an ISBN number which is the number we can refer to. However ISBN number in table Authors and table Publishers are not mandatory so we can have nulls.

I need to construct a query that will display everything from table Library, Publishers and Authors so basically I need to join them together.

Is it possible to have a query that will output the following from all three tables:

ISBN number Book Name Pages Borrowed Rate Date FirstName LastName Publisher
032121353X Visual Quickstart Gu 1239 Y 1 01/01/1999 Weinman Braun Ace Books
1111111111 JavaBooks null null null null null null Sheridan
321199553 null null null 3 null Weinman Braun Academic P
9999999999 null null null 4 null Carson Yarbrough Addison-We
014042430X Complete Writings 650 Y 5 11/27/1999 null null null
032121353X Visual Quickstart Gu 1239 Y 1 01/01/1999 null null null
080712320X Running A Thousand M 830 N 1 02/23/2000 null null null
130082167 Cost Accounting 539 N 3 10/23/2000 null null null
130082171 Management Accountin 388 N 1 12/06/2000 null null null
131430211 Financial Reporting 232 N 3 08/04/2005 null null null
131475912 Accounting Informati 1810 N 3 08/20/2002 null null null
131475916 Accounting Informati 2047 N 5 12/18/2002 null null null
131495380 Cost Accounting 2521 N 4 08/15/2003 null null null
222222222 null null null null null null null Sheridan
111111111 null null null null null null null Sheridan
471412856 null null null null null Hopkins Paige null
9999999999 null null null null null Carson Yarbrough null
870495275 null null null null null Robinson Yarbrough null
null null null null null null Blazek Paige null
null null null null null null Walker Yarbrough null

So print everything from Library, Publishers, Authors where ISBN are equaled.

Then check Publishers and Authors for ISBN's that are equeled.

Then print out everything else from Library, Authors and Publshers.

I've been trying to many different queries and nothing is giving me this output. Is it even possible?

Please help me out, it would be greatly appreciated.

Thank you.

Regards,

Irena.

 

[antic]

Yes, it's "SQL 101" to do that sort of thing, but it's also "SQL 101" not to structure your data like that. It simply won't work using the ISBN numbers like that.

The main issue is you can't match records in related tables by a field where the value may be null (your ISBN numbers). You can't match a null ISBN author to the right null ISBN book - that does make sense. Table relationships must be "normalised", and 99.9% of the time it's done using Identity fields.

This page might kick you off in the right direction:
http://www.devx.com/getHelpOn/10MinuteSolution/20552/1954

 

[antic]

I needed a distraction from work, so here's my solution. Attached is an Access MDB representing your data. Not all the data is in, but the structure is all there.

The basic idea in normalisation is that you try not to duplicate your data. You currently have ISBN duplicated in multiple tables, which can be source of problems later on down the track. For example, say you make a mistake assigning an ISBN number to a book - you then have to go and change that number for your Authors and Publishers as well, which is not a good situation. Never relate tables to each other using values that can change, be subject to typos or take up too much space. An Identity field for every table is the way to go. They auto-increment and once they're assigned to a record they never change.

Since ISBNs are assigned to books, that's the *only* table where they should be stored. As said above, each Library record will have an Identity field, as will each Author records. This is how the tables refer to each other. Since there is one Publisher for each book, the Library table will include the PubID field, so it can refer to its correct publisher. That's called a "one to many" relationship - one publisher for many books. Each book record refers back to its "parent" publisher record.

Now for something more complicated. You have a "many-to-many" relationship between Books and Authors - that is, there can be more than one author for a book, but also more than one book to an author. A common way to manage this relationship is via a separate table which simply stores the following info: BookID, AuthorID. Each record of that table describes a relationship between a single book and a single author. You can query that table to find all the authors for a book, and also all the books for an author.

You also seem to have a relationship between Author and Publisher without any books involved (ISBN 321199553 etc). Don't know if that's intentional or not, but if you want to relate Authors to Publishers, that's a simple matter of adding PubID to the Author table. Again, that's a "one to many" relationship - one Publisher for many Authors, so each Author points back to its parent Publisher record.

This should give you a start anyway and an idea of how it's supposed to work. Hope it helps. Took about 30 minutes so the fee is $500, I'll PM you my account details.

Now everyone can criticise MY design. There's often more than one way to do things, depending on requirements, but there are definite things to avoid in database design.

 

[irenavassilia]

Unfortunetly my Java teacher created this table design which I really disagree with too. It simply doesnt make sense to have Authors and Publishers not related to a book at all. What is the point of inserting them lol.. because he didnt even specify that we would have to update the Authors or Pusblishers table if a user enters an ISBN and an Authors or Publisher that's in the tables but doesnt have an ISBN.

Very strange design!... So thank you guys for trying, but I do have a query that pulls authors and publishers matching an isbn or they dont, duplicates or not I have an exam next Monday and I think I'm spending way to much time on a query thats pretty complicating. This is a small part of the assignment so hopefully he doesnt take that much marks off.

Regards,

Irena.