CODEWITHSUNDEEP
mysql
Sujith
Sujith

Reputation: 17

How to use if query in where clause

How to change the FOREIGN KEY using if in where clause 

SELECT u.id,u.first_name, m.text,m.msg_id,m.u_from_id,m.u_to_id 
FROM user u 
JOIN messages m 
ON u.id= m.u_from_id 
WHERE m.ad_id=$ad_id 
    AND m.msg_id>'$last_msg_id' 
GROUP BY m.u_from_id 
ORDER BY m.u_from_id

This query is working perfectly, but I want some modification

if m.u_from_id='6' then check u.id=m.to_id instead of u.id= m.u_from_id

(if the FOREIGN KEY(m.u_from_id) value='6' then change the FOREIGN KEY to m.to_id)

Upvotes: 0

Views: 40

Answers (1)

Psi
Psi

Reputation: 6783

There is no IF in mysql as it is known from for example MS Access, the equivalent, you may want to use, is CASE:

SELECT u.id,u.first_name, m.text,m.msg_id,m.u_from_id,m.u_to_id 
FROM user u 
JOIN messages m 
ON CASE WHEN m.u_from_id = 6 THEN u.id=m.to_id ELSE u.id= m.u_from_id END
WHERE m.ad_id=$ad_id 
    AND m.msg_id>'$last_msg_id' 
GROUP BY m.u_from_id 
ORDER BY m.u_from_id

Upvotes: 1

Related Questions