CODEWITHSUNDEEP
sqlsql-servert-sqljoin
raddevus
raddevus

Reputation: 9087

How can I query denormalized table and get associated data in result-set?

I have a table which contains some denormalized data something like the following (fake to simplify my question):

Book Table

ID, Title, Author-Name, Publisher-Name, Category

In another table I have something like the following:

Author Table

ID, Author-Name, Address

Publisher Table

ID, Publisher-Name, Address

Assume that Publisher-Name and Author-Name are always unique.

Desired Result-Set

Now, all I want to do is create a query that creates a result-set that includes :

 1. all columns from Book table
 2. Author.ID 
 3. Publisher.ID

The final result set would look something like the following:

Result-set Columns

ID, Title, Author-Name, Author.ID as [AuthorID], Publisher-Name, Publisher.ID as [PublisherID] Category

Let's say there are two rows in the Book Table, then the result-set would have two rows which include the appropriate values for the Author.ID and Publisher.ID, because the lookup has been done in the query and returned in the result-set.

What I've Tried : join

I've tried using various joins but I always get more rows than just two rows, because the join seems to join on the Publisher.ID AND the Author.ID and I get 1 row for each of them which ends up giving me 4 rows instead of 2.

Sample Data

Books

1, 'All The Time', 'Fred Smith', 'Big Pub Co.', 'non-fiction'
2, 'Biggest Title Ever', 'John Jones', 'Small Pub Co.', 'fiction'

Authors

100, 'Fred Smith', 'Yukon, AK'
101, 'John Jones', 'Happy, VT'

Publishers

300, 'Big Pub Co', 'Angry, IL'
301, 'Small Pub Co', 'Someplace, IN'

Expected Result-Set

1, 'All The Time', 'Fred Smith', 100, 'Big Pub Co.', 300, 'non-fiction'
2, 'Biggest Title Ever', 'John Jones', 101, 'Small Pub Co.' 301, 'fiction'

Upvotes: 0

Views: 96

Answers (3)

Larry Lustig
Larry Lustig

Reputation: 51000

So, a few things:

  1. Your data is not denormalized, it is (correctly) normalized.
  2. You have made an odd decision to store author name and publisher name in the books table. This will cause problems if you have two authors or publishers with the same name in their respective tables. If that is guaranteed never to happen (that is, you require a variant of the name in the case of same-name authors or publishers) then you can get rid of the ID column in their respective tables.
  3. The solution to your problem is to use a JOIN as you deduced, but we can't tell where you made your mistake because you did not show what you tried.

All that said, it sounds like you accidentally performed a cartesian JOIN, which is where you join multiple tables but fail to provide the join condition.

So, I suspect you wrote something along the lines of (there are different ways to express this):

FROM Books JOIN Authors JOIN Publishers

and you should have written:

FROM Books INNER JOIN Authors ON Books.Author-Name = Authors.Author-Name
   INNER JOIN Publishers ON Books.Publisher-Name = Authors.Publisher-Name

Upvotes: 2

pi.314
pi.314

Reputation: 620

Instead of Inner i would suggest you left join in case there won't be match between tables.

SELECT
    ...
FROM
    BookTable BOOK
    LEFT JOIN
    AuthorTable AT
        ON AT.Author-Name = BOOK.Author-Name
    LEFT JOIN
    PublisherTable PT
        ON PT.Publisher-Name = BOOK.Publisher-Name

However for take sure your rows from Book Table wont be multiplied: use outer apply:

SELECT
    BOOK.ID,
    BOOK.Title,
    BOOK.Author-Name,
    AT.ID AS AuthorID,
    BOOK.Publisher-Name,
    PT.ID AS PublisherID,
    BOOK.Category
FROM
    BookTable BOOK
    OUTER APPLY (SELECT TOP 1 ID FROM AuthorTable AT WHERE AT.Author-Name = BOOK.Author-Name) AT
    OUTER APPLY (SELECT TOP 1 ID FROM PublisherTable PT WHERE PT.Publisher-Name = BOOK.Publisher-Name) PT

Upvotes: 0

D-Shih
D-Shih

Reputation: 46249

You can try JOIN` and get your want columns from those tables.

SELECT 
    b.ID, 
    b.Title,
    b.[Author-Name],
    a.id,
    p.[Publisher-Name],
    p.id,b.Category
FROM Book b 
JOIN Author a on b.[Author-Name] = a.[Author-Name]
JOIN  Publisher p ON b.[Publisher-Name] = p.[Publisher-Name]

Upvotes: 2

Related Questions