SQL stored procedure on multiple tables

Question

I'm building asp.net mvc app, and I'm having trouble writing SQL stored procedure on multiple tables.

I have 7 different tables, all connected to each other.

Customer
    Id
    Name

CustomerBook
    Id
    CustomerId
    BookId

Book
    Id
    Name

BookType
    Id
    BookId
    TypeId

Type
    Id
    Name

BookCategory
    BookId
    CategoryId

Category
    Id
    Name

It looks something like that.

CustomerBook.CustomerId = Customer.Id 
CustomerBook.BookId = Book.Id
BookCategory.BookId = Book.Id
BookCategory.CategoryId = Category.Id
BookType.BookId = Book.Id
BookType.TypeId = Book.Id

if im not mistaken.

What i want to do now, is write stored procedure that would get and display all the books that was "bought" by specific user.

I would like to display:

Book name
Book type
Book category

...for each and every user, that is currently logged into session.

Later i would like to get data into controller...but thats a problem for another day.

Since im pretty new to procedures and only know a little SQL, i would really appreciate your help!

This is what i've tried so far:

CREATE PROCEDURE [dbo].[getBookByCustomerId]
@Id int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM [Customer] WHERE Id = @id
END

and now im stuck...

You should take a SQL tutorial. These are basic joins and this has nothing to do with procedures since you just put the select into the procedure in the end. — juergen d, Jul 17 '17 at 09:54
i know how to join, since i already did that above. i want to write procedure, but i've only found simple one query procedure tutorials online @juergend — aiden87, Jul 17 '17 at 10:13
is the above tables and relations not good enough? @ManishSingh — aiden87, Jul 17 '17 at 10:13
sql server or mysql? They are totally different products and the detail of any answer could be different. Please don't add irrelevant tags - the MVC and ASP.NET ones don't appear to come into this either. — ADyson, Jul 17 '17 at 10:25
"i know how to join, since i already did that above". Did you? Where? I can't see a join anywhere in your SQL. e.g. `SELECT * FROM Customer C INNER JOIN CustomerBook CB ON C.Id = CB.CustomerId`. I didn't see you write that, but that's what you need to do in order to get results from multiple tables. And that's not really related to stored procedures specifically, it's just general SQL. — ADyson, Jul 17 '17 at 10:28

score 2 · Accepted Answer · answered Jul 17 '17 at 10:27

Something like this should do it:

CREATE PROCEDURE [dbo].[getBookByCustomerId]
    @Id INT
AS
     BEGIN
         SET NOCOUNT ON;
         SELECT
             B.Id,
             B.Name,
             T.Name,
             C.Name
         FROM Book B
         INNER JOIN CustomerBook CB ON B.Id = CB.BookId
         INNER JOIN BookType BT ON B.ID = BT.BookID
         INNER JOIN Type T ON BT.TypeID = T.ID
         INNER JOIN BookCategory BC ON B.BookId = BC.BookId
         INNER JOIN Category C ON BC.CategoryId = C.CategoryId
         WHERE CB.CustomerID = @Id;
     END;

It will get the Book ID and Name where the customerID is equal to the values that is passed in.

The INNER JOIN will only get records that are in both tables, here is a good post to understand joins.

score -1 · Answer 2 · answered Jul 17 '17 at 10:29

Here parameter @Id stands for customer id.

CREATE PROCEDURE [dbo].[getBookByCustomerId]
@Id int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
    B.Id, B.Name, T.Name, C.Name 
    FROM 
    Customer C
    INNER JOIN 
    CustomerBook CB ON C.Id = CB.CustomerId
    INNER JOIN
    Book B ON B.Id = CB.Id
    INNER JOIN
    BookType BT ON B.Id = BT.BookId
    INNER JOIN 
    Type T ON BT.TypeId = T.Id
    INNERJOIN
    BookCategory BC ON
    BC.BookId = B.BookId
    INNER JOIN
    Category CT on CT.Id = BC.CategoryId

    WHERE 
    C.Id = @Id  

END

SQL stored procedure on multiple tables

2 Answers2