x

Inner Join SubQuery

Hello everyone,

The PEACE of the Lord Jesus be with you all.

I have five tables, and would like some help to create a query that returns all the information only once, without repetition. A table containing books, other Publishers, Authors, stock, Authors and Books.

The problem I am encountering is how to bring a select only the name of the authors of the book.

SELECT  Books.BookID, Books.SellerID, Books.PublisherID, Books.Title, Books.Price,
    Publisher.PublisherID, Publisher.PublisherName,
    Author.AuthorID, Author.AuthorName,
    Stock.BookID, Stock.TypeID, Stock.Quantity
FROM    Books
INNER JOIN
    ON Publisher.PublisherID = Books.PublisherID
INNER JOIN
    ON Stock.BookID = Books.BookID
WHERE
    Books.Title LIKE 'SQL%'

How can I do to bring Author´s name only for books tah match?

GOD Bless You.

*

---------------------------*
|           Books           |
|---------------------------|
| Column Name | Data Type   |
|-------------+-------------|
| BookID      | int         |<------------*
| SellerID    | int         |         |   |
| PublisherID | int         |-----*   |   |
| Title       | varchar(60) |     |   |   |
| Price       | money       |     |   |   |
*---------------------------*     |   |   |
                                  |   |   |
*-----------------------------*   |   |   |
|          Publisher          |   |   |   |
|-----------------------------|   |   |   |
| Column Name   | Data Type   |   |   |   |
|---------------+-------------|   |   |   |
| PublisherID   | int         |<--*   |   |
| PublisherName | varchar(50) |       |   |
*-----------------------------*       |   |
                                      |   |
*-----------------------------*       |   |
|        Authror_Books        |       |   |
|-----------------------------|       |   |
| Column Name   | Data Type   |       |   |
|---------------+-------------|       |   |
| KeyID         | int         |       |   |
| BookID        | int         |<------*   |
| AuthorID      | int         |<--*       |
*-----------------------------*   |       |
                                  |       |
*-----------------------------*   |       |
|          Authror            |   |       |
|-----------------------------|   |       |
| Column Name   | Data Type   |   |       |
|---------------+-------------|   |       |
| AuthorID      | int         |<--*       |
| AuthorName    | varchar(50) |           |
*-----------------------------*           |
                                          |
*-----------------------------*           |
|           Stock             |           |
|-----------------------------|           |
| Column Name   | Data Type   |           |
|---------------+-------------|           |
| StockID       | int         |           |
| BookID        | int         |<----------*
| TypeID        | int         |
| Quantity      | int         |
*-----------------------------*   
more ▼

asked Apr 21 at 10:27 PM in Default

Marcos Cruz gravatar image

Marcos Cruz
12 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Meaning that a single author can have multiple books (one to many relationship)?

If you wanted to add to the query that you already have, something like this should work:

SELECT  Books.BookID, Books.SellerID, Books.PublisherID, Books.Title, Books.Price,
    Publisher.PublisherID, Publisher.PublisherName,
    Authror.AuthorID, Authror.AuthorName,
    Stock.BookID, Stock.TypeID, Stock.Quantity
FROM    Books
INNER JOIN Publisher ON Publisher.PublisherID = Books.PublisherID
INNER JOIN Stock ON Stock.BookID = Books.BookID
INNER JOIN  Authror_Books on Books.BookID = Authror_Books.BookID
INNER JOIN Authror on Authror_Books.AuthorID = Authror.AuthorID
WHERE
    Books.Title LIKE 'SQL%'

Note that I keep the spelling of the tables/columns identical to what you provided.

Hope that helps!
more ▼

answered Apr 22 at 12:27 AM

JohnM gravatar image

JohnM
6.6k 1 3 7

(comments are locked)
10|1200 characters needed characters left

Something like this should work. You can add a group by and then include the aggregate for the Quantity.

SELECT  Books.BookID, Books.SellerID, Books.PublisherID, Books.Title, Books.Price,
        Publisher.PublisherID, Publisher.PublisherName,
        Authror.AuthorID, Authror.AuthorName,
        Stock.BookID, Stock.TypeID, SUM(Stock.Quantity)
    FROM    Books
    INNER JOIN Publisher ON Publisher.PublisherID = Books.PublisherID
    INNER JOIN Stock ON Stock.BookID = Books.BookID
    INNER JOIN  Authror_Books on Books.BookID = Authror_Books.BookID
    INNER JOIN Authror on Authror_Books.AuthorID = Authror.AuthorID
    WHERE
        Books.Title LIKE 'SQL%'
    GROUP BY
        Books.BookID, Books.SellerID, Books.PublisherID, Books.Title, Books.Price,
        Publisher.PublisherID, Publisher.PublisherName,
        Authror.AuthorID, Authror.AuthorName,
        Stock.BookID, Stock.TypeID 

I don't have any data to verify with, but this should work.

Hope this helps!!
more ▼

answered Apr 22 at 03:08 PM

JohnM gravatar image

JohnM
6.6k 1 3 7

Hi John,

I fired my brain and solve the question number 1, thanks for tip!

I´m using SQL Server 2008 R2, and right now can´t change. Can you help me with question number 2, pagging?

Thanks a lot.
Apr 23 at 05:24 PM Marcos Cruz

His John,

Thanks for your answer, it works perfectly.

I now have another problem. The table has a Stock TypeID field, where it is stored for example set, consignment stock and own stock.

I would like to bring the sum of these stocks in the same line. This requires a subquery, how can it be implemented, inthe main select or inner join?

SELECT SUM(Stock.Quantity) AS AvaiableStock FROM Stock WHERE (Stock.BookID = Books.BookID)

Thanks for your attention.
Apr 22 at 02:53 PM Marcos Cruz
I would start here: http://www.codeguru.com/csharp/.net/net_data/article.php/c19611/Paging-in-SQL-Server-2005.htm and modify as you need it. The article refers to SQL Server 2005 but the concepts should easily transfer to 2008.
Apr 23 at 06:17 PM JohnM
(comments are locked)
10|1200 characters needed characters left

His John,

My problem now is with the pagination of results. In a search result may contain many books, so I thought:

1 - I find the total number of records returned by the query, and will use this information just notify the user. I used the following:

SELECT COUNT(*) AS Total FROM Books INNER JOIN Publisher ON Publisher.PublisherID = Books.PublisherID INNER JOIN Stock ON Stock.BookID = Books.BookID INNER JOIN Authror_Books on Books.BookID = Authror_Books.BookID INNER JOIN Authror on Authror_Books.AuthorID = Authror.AuthorID WHERE Books.Title LIKE 'SQL%'

I have only one book registered in my database test, but this query returns Total = 2. How is this possible?

2 - Then I thought with the result of the previous query can create a Stored Procedure that returns just a number of records per page, using SELECT ROW_NUMBER () OVER,

but my brain crash.

SELECT Books.BookID, Books.SellerID, Books.PublisherID, Books.Title, Books.Price, Publisher.PublisherID, Publisher.PublisherName, Authror.AuthorID, Authror.AuthorName, Stock.BookID, Stock.TypeID, SUM(Stock.Quantity) FROM ( SELECT ROW_NUMBER() OVER(ORDER BY Books.Title ASC) AS row, * FROM Books INNER JOIN Publisher ON Publisher.PublisherID = Books.PublisherID INNER JOIN Stock ON Stock.BookID = Books.BookID INNER JOIN Authror_Books on Books.BookID = Authror_Books.BookID INNER JOIN Authror on Authror_Books.AuthorID = Authror.AuthorID WHERE Books.Title LIKE 'SQL%' GROUP BY Books.BookID, Books.SellerID, Books.PublisherID, Books.Title, Books.Price, Publisher.PublisherID, Publisher.PublisherName, Authror.AuthorID, Authror.AuthorName, Stock.BookID, Stock.TypeID ) AS tbl WHERE row > 1 AND -- return 1 page with 25 records row < 25

Can you help me with these questions?

Thanks for your attention.
more ▼

answered Apr 22 at 07:46 PM

Marcos Cruz gravatar image

Marcos Cruz
12 1 1 2

For question 1) you might have only one book, but with the inner joins you most likely have a duplicate row in another table with a duplicate ID. 2 author's, 2 publishers, 2 stocks, etc. There's an extra row somewhere.

For question 2) what version of SQL Server you using? SQL 2012 introduce some new ways to handle pagination.

Reference: http://www.mssqlgirl.com/paging-function-performance-in-sql-server-2012.html
Apr 22 at 08:30 PM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x19

asked: Apr 21 at 10:27 PM

Seen: 261 times

Last Updated: Apr 23 at 06:17 PM