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, 2014 at 10:27 PM in Default

avatar image

Marcos Cruz
12 2 2 4

(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, 2014 at 12:27 AM

avatar image

JohnM
12.3k 3 7 14

(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, 2014 at 03:08 PM

avatar image

JohnM
12.3k 3 7 14

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, 2014 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, 2014 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, 2014 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, 2014 at 07:46 PM

avatar image

Marcos Cruz
12 2 2 4

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, 2014 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.

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:

x29

asked: Apr 21, 2014 at 10:27 PM

Seen: 564 times

Last Updated: Apr 23, 2014 at 06:17 PM

Copyright 2016 Redgate Software. Privacy Policy