0

I'm currently building a search app with Nodejs and Mysql. My database is fully set up and the node app is also fully setup. However, I'm having a few issues.

I'm trying to implement something like "search for a school, a course or a user".

Now, schools are in a different table of their own, courses are in a different table of theirs and users are also in a different table of theirs. These tables are not related in any way.

I want a way to search all 3 tables and if the search query is a school, I want to return all the schools that match the search query. Likewise, if the search query is a course, I want to return all the courses that match the search query. Also, if the search query is a user, I want to return all the users that match the search query.

It is important to note that I'm searching only one column per table, which is the "name" column. So I need an SQL syntax to search all schools.name, courses.name and users.name with only one search query.

I have tried:

router.get('/search', function (req, res) {
  db.query('SELECT users.name AS userName, schools.name AS schoolName, course.name as courseName FROM users, schools, course HAVING userName like "%' + req.query.key + '%" OR schoolName like "%' + req.query.key + '%" OR courseName like "%' + req.query.key + '%"',
    function (err, rows, fields) {
      if (err) throw err;
      var data = [];
      for (i = 0; i < rows.length; i++) {
        if (rows[i].userName) {
          data.push(rows[i].userName);
        } else if (rows[i].schoolName) {
          data.push(rows[i].schoolName);
        } else if (rows[i].courseName) {
          data.push(rows[i].courseName);
        }
      }
      res.end(JSON.stringify(data));
    });
});

but it's not giving me what I want. It returns only the userName if the search query is a user but if the search query is a school or a course, it returns a list of ALL the usernames, schools and courses.

Please can someone help me with the correct SQL syntax to query because I've searched all over the internet and I can't seem to find a solution.

Awesome Bassey
  • 42
  • 7
  • 1
    You're using `HAVING` where you should be using `WHERE` – Phil Oct 10 '21 at 22:27
  • I used the WHERE clause already, but I kept getting "Unknown column userName in Where clause" so I found an article that said to use HAVING instead – Awesome Bassey Oct 10 '21 at 22:29
  • 2
    Before you complain that the duplicate question is for php while you are using node.js, let me tell you that the question is not really about application programming, this is about sql and that's the same for both questions. – Shadow Oct 10 '21 at 22:34
  • 1
    That's because your column name is `users.name` (and similarly for the other tables). You [can't use column aliases in a `WHERE` clause](https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause) – Phil Oct 10 '21 at 22:39
  • The referenced question actually helped me to fix it. Thank you. – Awesome Bassey Oct 10 '21 at 22:48

0 Answers0