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.

this looks like a disaster waiting to happen. Why wouldn't you write two distinct queries and rely on boolean logic in your application layer? — Woot4Moo, Jan 01 '14 at 19:15
making two separate queries for same task, if it can be achieved with one query — mdanishs, Jan 01 '14 at 19:21
@Woot4Moo take a look @ this answer: http://stackoverflow.com/questions/15875167/multiple-small-queries-vs-a-single-long-query-which-one-is-more-efficient — Mohammad Areeb Siddiqui, Jan 01 '14 at 19:24
@MohammadAreebSiddiqui good post, anecdotal at best with the caveat being, one round trip is better than several round trips. — Woot4Moo, Jan 01 '14 at 19:49

score 0 · Answer 1 · answered Jan 01 '14 at 19:10

0

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)

answered Jan 01 '14 at 19:10

adear11

935
6
11

Selecting from database, based on condition on different columns

1 Answers1