CODEWITHSUNDEEP
mysqlsqlnode.jsnode-mysql
Thomas
Thomas

Reputation: 2386

MySQL where if statement

I'm currently working on an API using node.js and node-mysql.

I start off by grabbing all the parameters that the client sends over like this:

  var userId = req.query.userId;
  var carType = req.query.carType;
  var hasOwnership = req.query.hasOwnership; (optional parameter)

What I would like to do is make a statement so that if the client sends the hasOwnership parameter with the request, my statement will return all the cars that belong to him with the specific car type. Similar to this:

SELECT * FROM cars WHERE car_type=? AND owner_id=?, [carType, userId];

Otherwise it will just return the all the cars that match the car type:

SELECT * FROM cars WHERE car_type=?, [carType];

Is it possible to accomplish something like this in a single query?

Thanks in advance!

Upvotes: 0

Views: 144

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You can phrase the query as something like this:

SELECT *
FROM car
WHERE (? is null or car_type = ?) AND
      (? is null or owner_id = ?);

Note that this version takes each parameter twice, unless you use named parameters.

Upvotes: 1

davejal
davejal

Reputation: 6133

A dirty quick solution would be to use an if then else.

if hasOwnership has value the use query #1 else use query #2

Upvotes: 0

Related Questions