CODEWITHSUNDEEP
sqlmysql
user319854
user319854

Reputation: 4106

mysql, using if

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)

Upvotes: 1

Views: 119

Answers (5)

Scott's Oasys
Scott's Oasys

Reputation: 154

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

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

Upvotes: 1

knittl
knittl

Reputation: 265151

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)
...

Upvotes: 1

Bill
Bill

Reputation: 4585

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;

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

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))

Upvotes: 0

the_void
the_void

Reputation: 5538

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)

Upvotes: 1

Related Questions