Servus
ich hab da ein kleines Problem.
ich möchte ein mysql querry bauen der über 4 Tabellen geht in 2 richtungen.
|
Source code
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT dkp_chars.char_id,
(CASE WHEN `raid_date` IS NOT NULL THEN `raid_date` ELSE 0 END) AS `last_raid`,
(CASE WHEN SUM(`raid_value`) IS NOT NULL THEN SUM(`raid_value`) ELSE 0 END) AS dkp_get,
(CASE WHEN SUM(`item_value`) IS NOT NULL THEN SUM(`item_value`)ELSE 0 END) AS dkp_pay
FROM `dkp_chars`
LEFT JOIN `dkp_chars_raid` ON dkp_chars.char_id = dkp_chars_raid.char_id
LEFT JOIN `dkp_chars_item` ON dkp_chars_item.raid_id = dkp_chars_raid.raid_id
LEFT JOIN `dkp_items` ON dkp_chars_item.item_id = dkp_items.item_id
LEFT JOIN `dkp_raids` ON dkp_chars_raid.raid_id = dkp_raids.raid_id
WHERE dkp_chars.char_id = '1' GROUP BY `char_id`
|
So gehts leider nicht da kommt
|
|
Source code
|
1
2
|
char_id last_raid dkp_get dkp_pay
1 1199645221 1005 -600
|
raus und eigentlich solte
|
|
Source code
|
1
2
|
char_id last_raid dkp_get dkp_pay
1 1199645221 405 -180
|
raus kommen.
die zwei einzelen Querys funktioniern ja
|
|
Source code
|
1
2
3
4
5
6
7
8
9
10
|
SELECT dkp_chars.char_id, (
CASE WHEN `raid_date` IS NOT NULL
THEN `raid_date`ELSE 0 END) AS `last_raid` ,
(CASE WHEN SUM( `raid_value` ) IS NOT NULL THEN SUM( `raid_value` ) ELSE 0 END ) AS dkp_get
FROM `dkp_chars`
LEFT JOIN `dkp_chars_raid` ON dkp_chars.char_id = dkp_chars_raid.char_id
LEFT JOIN `dkp_raids` ON dkp_chars_raid.raid_id = dkp_raids.raid_id
WHERE dkp_chars.char_id = '1'
GROUP BY `char_id`
|
ergebnis
|
|
Source code
|
1
2
|
char_id last_raid dkp_get
1 1199645221 405
|
und
|
|
Source code
|
1
2
3
4
5
6
7
8
9
|
SELECT dkp_chars.char_id, (
CASE WHEN SUM( `item_value` ) IS NOT NULL
THEN SUM( `item_value` ) ELSE 0 END ) AS dkp_pay
FROM `dkp_chars`
LEFT JOIN `dkp_chars_item` ON dkp_chars.char_id = dkp_chars_item.char_id
LEFT JOIN `dkp_items` ON dkp_chars_item.item_id = dkp_items.item_id
WHERE dkp_chars.char_id = '1'
GROUP BY `char_id`
|
ergebnis
|
|
Source code
|
1
2
|
char_id dkp_pay
1 -180
|
ich komm nicht drauf wie ich die Joins anordnen muss das es funktioniert
Coder Board Community »
Coding »
SQL »
