Selecting from database, based on condition on different columns

Question

I have the following query, I think the SQL CASE statements do not work in here. I want to select a field from a table, if one the conditions in the query gets true, the conditions are based on different columns. I think using case will not solve the problem because CASE checks for values in a particular column.

I can create two separate queries to achieve this task, is it possible to do it with one statement?

query:

SELECT 
    c.Conversation_ID, c.Title, c.Date, u.Name, 
    (SELECT 
        COUNT(*) 
     FROM 
        conversation_message 
     WHERE 
        Conversation_ID = c.Conversation_ID) as Total 
FROM 
    conversation c, user u 
WHERE 
    c.User_ID = ? 
    OR c.With_User_ID = ? 
    AND u.User_ID=c.With_User_ID

In the current scenario name is always of With_User_ID, what I want is if c.User_ID gets true then u.User_ID = c.With_User_ID should be executed, else if c.With_User_ID gets true, u.User_ID = c.User_ID should be executed.

Answer 1

Have you tried nesting conditions in your where clause?

For example:

WHERE
    (c.User_ID = ? AND u.User_ID = c.With_User_ID)
    OR (c.With_User_ID = ? AND u.User_ID = c.User_ID)