A mySQL query to check one option, and if not true, another option?

问题: I am very new at mySQL. I am doing a lookup of certain records from a table and the requirements have now changed. At first the query would pull the name of the person wh...

问题:

I am very new at mySQL.

I am doing a lookup of certain records from a table and the requirements have now changed. At first the query would pull the name of the person who was marked as the captain on the job plus a few other conditions. Now my boss wants me to pull the transcriber on the job if there is no captain. How can I do this in mySQL?

My code is:

$Q = $this->read_db->query('
        select DISTINCT j.id, j.reference, max(ja.FinalDue) as FinalDue, j.clientdue, j.pagecount, t.name, ja.iscaptain
            from `job` j 
            join `jobattachment` a on a.`jobid` = j.`id`
            join `jobassignment` ja on a.`jobid` = ja.`jobid`
            join `transcriber` t on ja.`transcriberid` = t.`id`
            where ja.`iscaptain` = 1 
            and ja.`deleted` != 1
            and (a.`editing` = 2 or a.`editing` = 3)
            and a.`redline` != 0
            and j.`complete` = 0
            group by j.id');
    return $Q->result_array();

How can I make the query find the captain, and only if there is no captain, find the person whose ID is in the redline field? The jobattachment table has multiple records for each job and if the attachment is marked as a redline, the transcriber's ID would be in that field, otherwise it would be empty.

I tried changing the query by adding an or, but I don't want the captain or the transcriber, only the transcriber if there is no captain. Any help would be much appreciated.


回答1:

select DISTINCT j.id, j.reference, max(ja.FinalDue) as FinalDue, j.clientdue, j.pagecount, case when ja.iscaptain then t.name else (select name from t where id = a.redline) end , ja.iscaptain
        from `job` j 
        join `jobattachment` a on a.`jobid` = j.`id`
        join `jobassignment` ja on a.`jobid` = ja.`jobid`
        join `transcriber` t on ja.`transcriberid` = t.`id`
        where 
        and ja.`deleted` != 1
        and (a.`editing` = 2 or a.`editing` = 3)
        and a.`redline` != 0
        and j.`complete` = 0
        group by j.id'

Explanation: First remove the ja.iscaptain = 1 part from the where clause so you can get all the people that also not captains.

Second, the t.name is changed according to the person if he is a captain or not, so instead of returning it hardcoded, you should check if its a captain then its name is t.name, else get the name from transcriber table by a.redline id.

I hope that I understood it correctly and it helps :)

  • 发表于 2019-03-05 03:14
  • 阅读 ( 183 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除