Chris Walker Chris Walker - 6 months ago 35
SQL Question

Condensing series of mySQL queries

I have a series of queries to find a question given a username. The queries find the userID associated with a username in the user table, then the questionID attached to the userID in the user_question table, then the question attached to the questionID in the question table.

Because my database structure follows the 2nd normal form and stores different entities in different tables (users, questions etc), it leads to needing multiple queries across multiple tables.

Is it possible to condense the series of queries into something which 1) requires less code and 2) requires less queries?

username = escape(req.query.username);

client.query("SELECT userID FROM user WHERE username = \"" + username + "\"", function(err, result) {
if (err !== null) {
console.log(err);
}
else {
client.query("SELECT questionID FROM user_question WHERE userID = " + result[0]["userID"], function(err, result) {
if (err !== null) {
console.log(err);
}
else {
client.query("SELECT question FROM question WHERE questionID = " + result[0]["questionID"], function(err, result) {
if (err !== null) {
console.log(err);
}
else {
//handle question
}
});
});
});

Answer

You can put your queries into one ...

something like this?

select 
    q.question 
from 
    question q
    JOIN user_question uq ON q.questionID = uq.questionID
    JOIN user u ON u.userID = uq.userID
WHERE 
    u.username = \"" + username + "\"

Btw... this looks like a potential sql injection part... \"" + username + "\"

Comments