返回json数据的存储过程

I'm pretty much new to SQL and stored procedures in general and I need help creating a stored procedure that would get data from database.

我对SQL和存储过程一般都很陌生，我需要帮助创建一个从数据库中获取数据的存储过程。

Here is my e-r diagram

这是我的e-r图

What I need to do, is get out all rented movies for each and every customer that is currently logged in.

我需要做的是为所有当前登录的客户购买所有租借的电影。

Here is example:

这是一个例子：

Customer:

顾客：

Id: 1
Name: Jack
Last name: Jackson

Id: 2
Name: John
Last name: Jankins

Movie

电影

Id: 1
Title: Logan
Serial num: 19946519

Id: 2
Title: Shutter island
Serial num: 23456519

Id: 3
Title: Pulp fiction
Serial num: 11934857

Result:

结果：

• RentedMovies for customer with id 1: (movies with id 1 and 2)
• ID为1的客户的RentedMovies :( ID为1和2的电影）

RentedMovies

RentedMovies

Id: 1
Name: John
Last name: Jankins
Title: Logan
Serial num: 19946519
Category: Action
Type: Limited release

Id: 2
Name: John
Last name: Jankins
Title: Shutter island
Serial num: 23456519
Category: Mystery
Type: Limited release

I'm not sure whether RentedMovies should both have Id of 1, but I think you get the picture of what I'm trying to achieve.

我不确定RentedMovies是否都应该具有1的Id，但我认为你可以了解我想要达到的目标。

This is what I've got so far, but it's not working. Plus I have no idea how to make it return as JSON.

这是我到目前为止所做的，但它不起作用。另外我不知道如何让它作为JSON返回。

SELECT 
    rm.id, c.name, c.lastName, m.title, m.serialNumber, cat.category, t.type
FROM 
    RentedMovies rm
INNER JOIN  
    Customer c ON c.id = rm.fk_Customer,
INNER JOIN  
    Movies m ON m.id = rm.fk_Movie
INNER JOIN  
    Category cat ON cat.id = mc.fk_Category
INNER JOIN  
    Type t ON t.Id = mt.fk_Type
WHERE 
    c.id = Id;

I would really appreciate your help!

我将衷心感谢您的帮助！

PS: this is an ASP.NET MVC app, and results should be based on user's currently logged in ID, which gets passed from code-behind.

PS：这是一个ASP.NET MVC应用程序，结果应该基于用户当前登录的ID，该ID从代码隐藏传递。

1 个解决方案

#1

This Should work!

这应该工作！

Create Proc RentedFilms (
@ID
) AS

 SELECT 
 CM.id
,CM.name
,CM.lastName
,MV.title
,MV.serialNumber
,CG.category
,TP.type
FROM RentedMovie RM
LEFT JOIN Customer CM on RM.fk_Customer=CM.id
LEFT JOIN Movie MV on  RM.fk_Movie=MV.id
RIGHT JOIN MovieCategory  MC on MC.fk_Movie=MV.id
LEFT JOIN Category CG on CG.id=MC.fk_Category
RIGHT JOIN MovieType MT on MT.fk_Movie=MV.id
LEFT JOIN Type TP on TP.id=MT.fk_Type
WHERE CM.id=@ID
FOR JSON AUTO