dongsi0625 2015-07-27 12:21
浏览 36
已采纳

MySQL:如果列值为2,则X ELSE执行Y.

The database I am working on right now is somewhat messy. I have three potential tables, that I want to join but in some cases it may only be two tables. Let's call these table1, table2 and table3.

table1 has a field called "type". If table1.type is 2, then I only need to join table3. For any other values I want to join table2 and then table3.

How can I achieve this in one single SQL query rather than: 1) having one query to select the type. 2) make a PHP foreach-loop to check the type of the current iteration and 3) perform a new query according to the type value.

Edit: I'll try to be more specific.

table1 has a column named "pid" that references to a whole other table, but that's redundant to this question. I tried working my ways around with UNIONs and LEFT JOINs but couldn't manage to achieve what I was looking for.

I want to select all results from my database with the "pid" value being "100". This gives me four rows in return, where was 2 of them are of type value "2" and the others are "1".

So basically what I want to achieve is the following two SQL statements in one:

(If "type" is "2")

SELECT *
FROM table1 t1
INNER JOIN table3 t3
ON t1.id = t3.t1_id
WHERE t1.pid = 100

(If "type" is NOT "2")

SELECT *
FROM table1 t1
INNER JOIN table2 t2
ON t1.id = t2.t1_id
INNER JOIN table3 t3
ON t2.id = t3.t2_id
WHERE t1.pid = 100

I'm guessing I could manage to do this with a UNION statement, but I'm confused on how to implement the WHERE t1.pid = '100' part.

  • 写回答

1条回答 默认 最新

  • dqbr37828 2015-07-27 12:32
    关注

    use an UNION e.g.

    SELECT t1.*, t3.*
       FROM table1 t1
       INNER JOIN table3 t3  ON t1.id = t3.t1_id
       WHERE t1.pid = 100 and t1.type = 2
    UNION
    SELECT t1.*, t3.*
       FROM table1 t1
       INNER JOIN table2 t2
       ON t1.id = t2.t1_id
       INNER JOIN table3 t3 ON t2.id = t3.t2_id
       WHERE t1.pid = 100 and t1.type <> 2;
    

    but it would be better to explicitly name the columns you want to get back.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题