CODEWITHSUNDEEP
mysqlasp.netsql-serverstored-proceduresmodel-view-controller
aiden87
aiden87

Reputation: 969

SQL stored procedure on multiple tables

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

Upvotes: 0

Views: 18565

Answers (2)

Maulik Modi
Maulik Modi

Reputation: 1306

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
    INNER JOIN BookCategory BC ON BC.BookId = B.BookId
    INNER JOIN Category CT ON CT.Id = BC.CategoryId
    WHERE C.Id = @Id    
END

Upvotes: -1

dbajtr
dbajtr

Reputation: 2044

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.

Upvotes: 2

Related Questions