mysql使用if子句从不同的表中选择

table: user

| userid | fullname |
|   1    |  admin   |
|   2    |  jimmy   |
|   3    |  anton   |
|   4    |  steven  |

table: maschinen

| maschine | maschine-name |
|    1     |       M1      |
|    2     |       M2      |
|    3     |       M3      |
|    4     |       M4      |

table: vorrichtungen

| id | maschine | vorrichtung | createdby |      datecreated    | updatedby |     dateupdated     |
| 1  |    1     |    11111    |     1     | 2012-06-23 17:10:25 |     1     | 2012-06-23 18:15:20 |
| 2  |    1     |    22222    |     1     | 2012-06-25 07:50:49 |     0     | 0000-00-00 00:00:00 |
| 3  |    2     |    33333    |     2     | 2012-06-27 10:22:29 |     0     | 0000-00-00 00:00:00 |
| 4  |    3     |    44444    |     2     | 2012-06-28 09:41:27 |     1     | 2012-06-28 12:40:37 |
| 5  |    4     |    11111    |     3     | 2012-06-29 15:53:47 |     0     | 0000-00-00 00:00:00 |
| 6  |    4     |    33333    |     4     | 2012-06-29 22:30:02 |     2     | 2012-06-29 22:30:02 |

my query looks like this:

我的查询看起来像这样:

SELECT
    vorrichtungen.`id`, 
    maschinen.`maschine-name` AS maschine, 
    vorrichtungen.`vorrichtung`, 
    vorrichtungen.`createdby`, 
    user.`fullname` AS creator, 
    vorrichtungen.`datecreated`, 
    vorrichtungen.`updatedby`, 
    vorrichtungen.`dateupdated`
FROM 
    vorrichtungen,maschinen,user
WHERE 
    maschinen.`maschine` = vorrichtungen.`maschine` 
AND 
    vorrichtungen.`createdby` =  user.`userid` 

and i recieve this result:

我收到了这个结果:

| id | maschine | vorrichtung | createdby | creator |      datecreated    | updatedby |     dateupdated     |
| 1  |    M1    |    11111    |     1     |  admin  | 2012-06-23 17:10:25 |     1     | 2012-06-23 18:15:20 |
| 2  |    M1    |    22222    |     1     |  admin  | 2012-06-25 07:50:49 |     0     | 0000-00-00 00:00:00 |
| 3  |    M2    |    33333    |     2     |  jimmy  | 2012-06-27 10:22:29 |     0     | 0000-00-00 00:00:00 |
| 4  |    M3    |    44444    |     2     |  jimmy  | 2012-06-28 09:41:27 |     1     | 2012-06-28 12:40:37 |
| 5  |    M4    |    11111    |     3     |  anton  | 2012-06-29 15:53:47 |     0     | 0000-00-00 00:00:00 |
| 6  |    M4    |    22222    |     4     |  steven | 2012-06-29 22:30:02 |     2     | 2012-06-29 22:30:02 |

i try to explain what i want..

我试着解释我想要的东西..

if updatedby !=0 than select fullname from table user AS updatetor else 0 or something like that .. but i want all 6 rows

如果更新了!= 0而不是从表用户AS updatetor中选择fullname 0或类似的东西..但我想要所有6行

i want to receive this result:

我想收到这个结果:

| id | maschine | vorrichtung | createdby | creator |      datecreated    | updatedby | updatetor |     dateupdated     |
| 1  |    M1    |    11111    |     1     |  admin  | 2012-06-23 17:10:25 |     1     |   admin   | 2012-06-23 18:15:20 |
| 2  |    M1    |    22222    |     1     |  admin  | 2012-06-25 07:50:49 |     0     |      0    | 0000-00-00 00:00:00 |
| 3  |    M2    |    33333    |     2     |  jimmy  | 2012-06-27 10:22:29 |     0     |      0    | 0000-00-00 00:00:00 |
| 4  |    M3    |    44444    |     2     |  jimmy  | 2012-06-28 09:41:27 |     1     |   admin   | 2012-06-28 12:40:37 |
| 5  |    M4    |    11111    |     3     |  anton  | 2012-06-29 15:53:47 |     0     |      0    | 0000-00-00 00:00:00 |
| 6  |    M4    |    22222    |     4     |  steven | 2012-06-29 22:30:02 |     2     |   jimmy   | 2012-06-29 22:30:02 |

is it possible to realize that? thanks for your help :)

有可能意识到这一点吗?谢谢你的帮助 :)

regards berni

2 个解决方案

#1

SELECT
    v.`id`, 
    m.`maschine-name` AS maschine, 
    v.`vorrichtung`, 
    v.`createdby`, 
    v.`datecreated`,
    creator.fullname as creator,
    v.`updatedby`,
    case when v.updatedby is null then '0' else updator.`fullname` end AS updatetor,
    v.`dateupdated`
from
    vorrichtungen v
    inner join
    user creator on creator.userid = v.createdby
    left join
    user updator on updator.userid = v.updatedby
    inner join
    maschinen m on m.maschine = v.maschine 
order by datecreated

#2

this will return you something closer to what you want.

这会让你更接近你想要的东西。

SELECT
vorrichtungen.`id`, 
maschinen.`maschine-name` AS maschine, 
vorrichtungen.`vorrichtung`, 
vorrichtungen.`createdby`, 
creatortable.`fullname` AS creator, 
vorrichtungen.`datecreated`, 
vorrichtungen.`updatedby`, 
updatetortable.`fullname` AS updatetor ,
vorrichtungen.`dateupdated`
FROM 
vorrichtungen,
maschinen,
user as creatortable ,
user as updatetortable 
WHERE 
vorrichtungen.createdby =  creatortable.userid
AND
vorrichtungen.updatedby =  updatetortable.userid
AND
vorrichtungen.id        =   maschinen.maschine