MySQL Query depending on column value define where statement

Question

I have this SQL:

SELECT
  `refnumbers`.`order_id`,
  `refnumbers`.`deal_id`,
  `refnumbers`.`claim_track_id`,
  `deals`.`partner_count`
FROM `refnumbers`
  JOIN `deals`
    ON (`refnumbers`.`deal_id` = `deals`.`ID`)
  JOIN `users`
    ON (`users`.`id` = `deals`.`partner_id`)
WHERE `refnumbers`.`is_claimed` = '1'
    AND `deals`.`partner_id` = '62039'
    AND `refnumbers`.`claimed_at` BETWEEN '2013-01-17 00:00:00'
    AND '2013-01-17 23:59:59'
ORDER BY `refnumbers`.`claimed_at` DESC

I would like to select where deals.partner_id = 62039 IF deals.partner_count is 1.

If partner_count isnt 1 then it should select where refnumbers.claim_track_id = 62039

How can this be done?

You can use conditional MySQL keyword CASE WHEN...THEN...END — Bud Damyanov
– Bud Damyanov, Commented Jan 17, 2013 at 13:09

Raphaël Althaus · Accepted Answer · 2013-01-17 13:08:15Z

2

try

where
case when deals.partner_count = 1 
   then deals.partner_id = 62039
   else refnumbers.claim_track_id = 62039
end

answered Jan 17, 2013 at 13:08

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

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

Comments

John Woo · Accepted Answer · 2013-01-17 13:06:30Z

1

You need to have a condition enclose in a parenthesis which uses OR

SELECT  `refnumbers`.`order_id`, 
        `refnumbers`.`deal_id`, 
        `refnumbers`.`claim_track_id`, 
        `deals`.`partner_count`
FROM    `refnumbers` 
        INNER JOIN `deals` 
            ON (`refnumbers`.`deal_id` = `deals`.`ID`) 
        INNER JOIN `users` 
            ON (`users`.`id` = `deals`.`partner_id`) 
WHERE   `refnumbers`.`is_claimed` = '1'  AND 
        `refnumbers`.`claimed_at` BETWEEN '2013-01-17 00:00:00' AND '2013-01-17 23:59:59' 
        AND
        (
            (deals.partner_id = 62039 AND deals.partner_count = 1)
            OR
            (refnumbers.claim_track_id = 62039 AND deals.partner_count <> 1)
        )
ORDER BY `refnumbers`.`claimed_at` DESC

answered Jan 17, 2013 at 13:06

John Woo

265k70 gold badges509 silver badges500 bronze badges

2 Comments

Hieu Le Over a year ago

MySQL CASE WHEN...THEN...END can do this in a more simple way :)

John Woo Over a year ago

@TrungHieuLe there are many ways to solved a problem, someone already posted which uses CASE.

Saharsh Shah · Accepted Answer · 2013-01-17 16:02:56Z

0

Try this:

SELECT r.order_id, r.deal_id,r.claim_track_id, d.partner_count
FROM refnumbers r
INNER JOIN deals d ON r.deal_id = d.ID
INNER JOIN users u ON d.partner_id = u.id 
WHERE IF(d.partner_count = 1, d.partner_id = '62039', r.claim_track_id = 62039) AND 
      r.is_claimed = '1' AND r.claimed_at BETWEEN '2013-01-17 00:00:00' AND '2013-01-17 23:59:59'
ORDER BY r.claimed_at DESC

answered Jan 17, 2013 at 16:02

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Collectives™ on Stack Overflow

MySQL Query depending on column value define where statement

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related