Relational to JSON - How to run two selects?

[naiieandrade]

I'm using tedious with sql-server, and I have to return a json file.
The #1 problem I had was because my select was returning 3 rows because was a relation one-to-many tables.

For example:

id	id_model_car
1	3
1	2
2	1
2	2
2	4
3	4

The select was some like

SELECT * FROM table where id=1

Was returning 3 rows, but it's was justing showing in Postman the last row because was being replaced.

And I need this return:

{
  "id": 200,
  "name": "Jennifer Whalen",
  "isSenior": true,
  "commissionPct": null,
  "jobHistory": [
    {
      "id": "AD_ASST",
      "departmentId": 90,
      "startDate": "17-SEP-1995",
      "endDate": "17-JUN-2001"
    },
    {
      "id": "AC_ACCOUNT",
      "departmentId": 90,
      "startDate": "01-JUL-2002",
      "endDate": "31-DEC-2006"
    }
  ]
} 

To do this, I think I need two selects. One to get id, name, isSenior, commissionPct and another to put in a list for jobHistory.

Do you can help me?

[Hadis-Fard]

@naiieandrade wondering whether you're using Tedious and do you have any issue or ambiguity with Tedious driver? 🤔 cause what you have posted seems like a specific SQL question related to project

[naiieandrade]

@Hadis-Fard Hi, sorry, I'm new with Tedious. I'm using Tedious. I'm doing a project and in shortly time I have to do this. So I'm studying and learning in this short period. I just want to know if I can do two select to return in the same JSON like as the example above. Is my question is clearer now? 😕

[naiieandrade]

Hey @Hadis-Fard I got it! I'm sure was not the correct form. How I said, I don't know yet.

router.get('/:id', authCheck.ensureLoggedIn(),
    function (request, response) {
        const personId = request.params.id;
        const connection = new tediousConnection(config.db);
        let kinds = [];
        connection.on('connect', function (err) {
            if (err) {
                // ....
            }
            else {
                  getKinds(personId, connection)
                      .then(data => {
                        kinds = data;

                        const connection1 = new tediousConnection(config.db);
                        connection1.on('connect', function (err) {
                            if (err) {
                                // ...
                            }
                            else {
                                getPersonIdInfo(personId, connection1)
                                    .then(data => response.status(200).json({ person:data, 'kinds':kinds }));
                            }
                        });
                  });
             }
        });
    });

This way I got to do 2 selects and concatenate in the same json.

My result was something like this:

{
    "person": {
        "id": 3,
        "name": "Mary",
        "age": 27,
    },
    "kinds": {
        "kind": [
            {
                "id": 5
            },
            {
                "id": 2
            },
            {
                "id": 3
            }
        ]
    }
}