MySQL where if statement

Question

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!

Why do you want to do that? When you have 2 query, the database make 2 different plan to execute them efficiently. If you combine them in a single query, it may that database can't plan to execute it efficiently. — tweetingReza
– tweetingReza, Commented Dec 5, 2015 at 5:11

Gordon Linoff · Accepted Answer · 2015-12-05 03:19:30Z

1

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.

answered Dec 5, 2015 at 3:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

davejal · Accepted Answer · 2015-12-05 02:20:20Z

0

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

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

answered Dec 5, 2015 at 2:20

davejal

6,19110 gold badges45 silver badges85 bronze badges

Collectives™ on Stack Overflow

MySQL where if statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related