How to get relation table data name instead of id

Question

I have two tables

Hero

id	name	universe_id
12	Mark	1
99	Glume	1

Universe

id	name
1	Andromeda
2	Aurora

How to return hero table data with universe name instead of universe_id. I'm sorry, I don't have enough experience in sql for this simple task. I read some answers in SO that need to use JOIN.

I try to do something like this.

exports.getHero = (req, res) => {
  
const sql = "SELECT * FROM `hero` WHERE `id`='" + req.params.id + "' JOIN `universe` WHERE `id`=`universe_id` ON `hero.universe_id`=`universe.id`";
    db.query(sql, (error, results) => {
        if (error) {
            console.log(error);
        } else {
            response.returnSingleValue(results, res);
        }
    });
};

But this is won't work.

UPD:

Expected result to be:

[{
  id: 12, 
  name: Mark,
  universe_id: Andromeda
},
{
  id: 99, 
  name: Glume,
  universe_id: Andromeda
}]

Your query is likely wide open to SQL injection attacks. Use your driver's mechanism for creating prepared statements/parameterized queries to mitigate this. Otherwise, it would likely be trivial to extract your entire database, make unauthorized modifications to the tables themselves, or irreversibly drop the entire database altogether. — esqew, Apr 07 '22 at 19:47
Is there a `UNIQUE` constraint on `\`universe``.\`name\``? If not, you'll find it particularly difficult to do this via the `name` attribute if there are more than one with the same `name` value. — esqew, Apr 07 '22 at 19:50
My advice regarding your SQL injection vulnerability has nothing to do with an authorization/authentication mechanism (at least within the scope of the question as you've put it). If your solution is to fix this "*later*", I'm not sure you're understanding the gravity of the security vulnerability you've introduced by electing to directly interpolate unsanitized user input into your query string. — esqew, Apr 07 '22 at 19:51
@esqew Will you please send any post about this, what should I do? Which side I need to dig? — Randall, Apr 07 '22 at 19:54
A quick Google search will yield several thousand helpful results, including [this SO thread](https://stackoverflow.com/questions/15778572/preventing-sql-injection-in-node-js) which points to some helpful resources on using the driver you appear to be using in your code to create parameterized queries/prepared statements to prevent such a vulnerability. — esqew, Apr 07 '22 at 19:55

score 1 · Accepted Answer · answered Apr 13 '22 at 18:51

1

const sql = "SELECT h.id, h.name, u.name as universe_id FROM `hero` as h LEFT JOIN `universe` as u ON h.universe_id=u.id";

answered Apr 13 '22 at 18:51

vanesicks

76
5

esqew · Answer 2 · 2022-04-07T20:07:00.690

0

You can JOIN universe to hero on the id value, then deliberately choose the fields in your initial SELECT clause which you want to see in your output:

SELECT `hero`.`id`, `hero`.`name`, `universe`.`name`
FROM `hero`
JOIN `universe` ON `hero`.`universe_id` = `universe`.`id`

edited Apr 07 '22 at 20:07

answered Apr 07 '22 at 19:55

esqew

42,425
27
92
132

I think you didn't understand my question.I need to return hero table with universe table. And instead of universe_id show universe name from table universe. Your sql code return universe table data with specific name – Randall Apr 07 '22 at 20:06
@Spectr Then you just need to be explicit with the fields in your initial `SELECT` - I've updated my answer to reflect this clarification. – esqew Apr 07 '22 at 20:07
Your sql code return me universe name instead of hero name. Sry. But this is not answer to my question. – Randall Apr 07 '22 at 20:17
"*Your sql code return me universe name instead of hero name.*" Can you elaborate further? The `name` attribute of the `hero` table is clearly in the `SELECT` expression, along with the `name` attribute of the `universe` table. It should return *both*, not one instead of the other. Can you edit your question to be explicit about what you expect a query that answers your question to return? – esqew Apr 07 '22 at 21:41
I updated my question. Add expected result. Sorry for late answer – Randall Apr 08 '22 at 13:59
@Spectr Ok, can you elaborate on how this answer doesn't meet that specification? – esqew Apr 08 '22 at 14:01
Of course. It return me this -> `[{id: 12, name: 'Andromeda' }]`. This is not an expected result – Randall Apr 08 '22 at 14:19

How to get relation table data name instead of id

2 Answers2