CODEWITHSUNDEEP
mysqlsqlselect
bernte
bernte

Reputation: 1184

mysql select from different tables with if clause

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

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

Upvotes: 3

Views: 1153

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

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

Upvotes: 2

Mansour Alnasser
Mansour Alnasser

Reputation: 5030

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

Upvotes: 1

Related Questions