(Mysql) How to mark different WHERE OR rows?

Question

For example i have requested:

WHERE (friend_id=? OR client_id=?)

How do i know which row meets friend_id condition and which meets client_id condition?

Is it possible to mark/flag rows depending of meeting condition somehow?

Thanks.

Answer 1

You can use UNION.

For example:

SELECT name, 1

FROM friends

WHERE friend_id=?

UNION

SELECT name, 0

FROM friends

WHERE client_id=?

Then when receiving data, you can check for that flag

Answer 2

SELECT *,
       'Friend' AS Source
  FROM TABLE
 WHERE friend_id = ?
UNION
SELECT *,
       'Client' AS Source
  FROM TABLE
 WHERE client_id = ?

But you'll have an issue if your entry is both friend and client. What do you want to happen in this case?

Answer 3

Use CASE operator

If knowing which row was hit because by any condition you can of course add this data to your result columns using case operators. The only downside is that your variables in your prepared statement (if that's what you're having here) are going to be doubled.

Answer 4

SELECT friend_id=? FROM yourtable WHERE (friend_id=? OR client_id=?);

You will get a true if the friend_id clause matches: Or even:

SELECT friend_id=?, client_id=? FROM yourtable WHERE (friend_id=? OR client_id=?);

To get both matches. In this way you can see if one or both matches.

Answer 5

You can use IF(), but then you have to bind one of id twice:

SELECT IF(friend_id=?, 'friend', 'client') AS type
FROM table
WHERE (friend_id=? OR client_id=?)

Answer 6

you can add flags to the query:

SELECT *, IF((friend_id=$friend_id), 1, 0) AS friend_matched, IF((client_id=$client_id), 1, 0) AS client_matched FROM table WHERE (friend_id=? OR client_id=?)