mysql, using if

Question

I have this sql query:

...
LEFT JOIN users ON
users.id = mod.id and mod.level = 1
...

But if don't found any result with mod.level = 1, i wish search with mod.data > 1 (users.id = mod.id and mod.data > 1)

Answer 1

You can switch your and to a WHERE and use an OR funtion:

LEFT JOIN users ON
users.id = mod.id 
WHERE mod.level = 1
OR mod.data > 1

Answer 2

You can additionally filter on the JOIN like this:

LEFT JOIN users ON users.id = mod.id AND (mod.level = 1 OR mod.data > 1)

Answer 3

Try something like this:

select *
from MyTable m
left outer join (
    select id
    from MyTable
    where level = 1
) ml on m.id = ml.id
left outer join users u on m.id = u.id
    and (u.id = m1.id or (m1.id is null and m.level > 1))

Answer 4

It may be slow but the join returns all rows that fit either case. Then uses the where clause to filter out the rows you don't want.

Select *
From 
  LEFT JOIN users ON users.id = mod.id AND (mod.level = 1 OR mod.data > 1) 
Where
  Case
    When mod.level = 1 then 1 
    When Not Exists(Select 1 from users Where users.id = mod.id and mod.level=1) 
      AND mod.data > 1 then 1
    Else 0 END = 1;

Answer 5

maybe using a XOR?

...
LEFT JOIN users
       ON users.id = mod.id
    WHERE mod.level = 1
      XOR mod.data > 1
...

this will get rows where mod.level is 1 or mod.data is greater than 1, but not rows where level is 1 and data is greater 1 at the same time

if you only want to look at mod.data when mod.level is not 1 use the following condition:

...
WHERE mod.level = 1
   OR (mod.level != 1
  AND mod.data > 1)
...