javascript - 通过express api连接两个表

Question

我正在使用 Express.js 构建一个 api。现在我的第一个睾丸正在工作。现在，我想从两个不同的表中获取值。例如，假设我有接下来的两张 table

Table_A
Id: 1,
Name: Chuck,
Age: 30

Table_B

Id: 1,
NickName: Chuckthulhu,
DateStart: 2018-11-01,
IdTableA: 1

我期望在我的 api 上返回的是:

{Id: 1, Name: "Chuck", NickName: "Chucthulhu", DateStart: "2018-11-01"}

我怎样才能做到这一点？这是我到目前为止的代码:

    var express = require("express");
var bodyParser = require("body-parser");
var sql = require("mssql");
var app = express();

app.use(bodyParser.json());

app.use(function (req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Methods", "GET,HEAD,OPTIONS,POST,PUT");
    res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, contentType,Content-Type, Accept, Authorization");
    next();
});

 var server = app.listen(process.env.PORT || 8080, function () {
    var port = server.address().port;
    console.log("App now running on port", port);
 });

 var dbConfig = {
     user:  "theUser",
     password: "thePassword",
     server: "theServer",
     database: "theDb"
 };

var  executeQuery = function(res, query){
     sql.connect(dbConfig, function (err) {
         if (err) {
                     console.log(err);
                     res.send(err);
                  }
                  else {
                         // create Request object
                         var request = new sql.Request();
                         // query to the database
                         request.query(query, function (err, result) {
                           if (err) {
                                      console.log(err);
                                      res.send(err);
                                     }
                                     else {
                                       res.send(result);
                                            }
                               });
                       }
      });
}

//GET API
app.get("/api/MyApi", function(req, res){
                var query = "select * from [Table_A]";
                executeQuery (res, query);
});

我对此很陌生。正如我所见，这是一个获取信息的简单查询...如果这是逻辑，我只需要对 Table_B 进行 join 或 left join 即可？ p>

我使用的是 Javascript、Node 和 Express.js，数据库位于 SQL Server 上提前致谢。

Answer 1

--From your example, IdTableA is the foreign key in Table B which relates it to a record
--in Table A, you would want to Inner Join on that to link it to Table A
--Alias your table''s with a and b and then select the appropriate columns from each

select a.[Id], a.[Name], b.[NickName], b.[DateStart] 
from [Table_A] AS a
INNER JOIN [Table_B] AS b
ON b.[IdTableA] = a.[Id]