MySQL - pomoc s dotazem
Ahoj, mám tento dotaz:
SELECT *, sms.id as myID, sms.jmeno as myNAME, users.jmeno jmeno, users.prijmeni prijmeni FROM sms LEFT JOIN users us ON us.zalozilID = sms.zalozilID INNER JOIN users ON sms.iduzivatele = users.zastupce OR users.id = sms.iduzivatele WHERE (odeslano = 1 AND sms.zalozilID = 100 AND platnaSMS = 1 OR users.zastupce = sms.iduzivatele AND odeslano = 1 AND platnaSMS = 1 OR sms.iduzivatele = 100 AND odeslano = 1 AND platnaSMS = 1) GROUP BY sms.id ORDER BY sms.id DESC
A potřebuji, aby mi v případě, že sms.iduzivatele = users.zastupce vypadl skutečný vkladatel SMS.
To znamená, aby upřednostňoval ve sloupečku users při výpise původního zakladatele s ID. Nikoliv tak jak je to teď že vypisuje
poslední jméno zastupitele. (Tedy aby vypisoval user.id name a ne user.zastupce (id) name.
Ale aspoň jsem to zkusil.
Jde o tuto podminku:
users.zastupce = sms.iduzivatele
Tak to pochopitelne vypise jmeno z radku users.zatupce ale ja chci tak, aby users.zastupce = user.id
Podivej se na prijaz CASE pomoci kteteho vyhodnotis, co potrebujes a dotahnes id, ktere potrebujes
Díky, pokročil jsem sem:
SELECT *, IF
(
users.zastupce < 1 THEN users.name, (SELECT users.prijmeni FROM users WHERE users.zastupce = users.id LIMIT 1)
) datass, sms.id as myID, sms.jmeno as myNAME, users.jmeno as jmeno, users.prijmeni as prijmeni FROM sms LEFT JOIN users us ON us.zalozilID = sms.zalozilID INNER JOIN users ON sms.iduzivatele = users.zastupce OR users.id = sms.iduzivatele AND (odeslano = 1 AND sms.zalozilID = 100 AND platnaSMS = 1 OR users.zastupce = sms.iduzivatele AND odeslano = 1 AND platnaSMS = 1 OR sms.iduzivatele = 100 AND odeslano = 1 AND platnaSMS = 1)
GROUP BY sms.id ORDER BY
sms.id DESC
Ale hlásí to:
Vaše syntaxe je nějaká divná blízko 'THEN users.name, (SELECT users.prijmeni FROM users WHERE users.zastupce = users.' na řádku 3
CASE WHEN, ziadny IF - https://www.w3schools.com/mysql/func_mysql_case.asp
Na serveru byly zjisteny chyby:
SELECT *, CASE WHEN users.zastupce > 0 THEN 'liska' ELSE 'nula' END , sms.id as myID, sms.jmeno as myNAME, users.jmeno as jmeno, users.prijmeni as prijmeni FROM sms LEFT JOIN users us ON us.zalozilID = sms.zalozilID INNER JOIN users ON sms.iduzivatele = users.zastupce OR users.id = sms.iduzivatele AND (odeslano = 1 AND sms.zalozilID = 100 AND platnaSMS = 1 OR users.zastupce = sms.iduzivatele AND odeslano = 1 AND platnaSMS = 1 OR sms.iduzivatele = 100 AND odeslano = 1 AND platnaSMS = 1) GROUP BY sms.id ORDER BY sms.id DESC
Ja se z toho po....
.\vendor\phpmyadmin\sql-parser\src\Utils\Query.php#443
Undefined property: PhpMyAdmin\SqlParser\Components\CaseExpression::$expr
Backtrace
.\libraries\parse_analyze.lib.php#30: PhpMyAdmin\SqlParser\Utils\Query::getAll(string 'SELECT *, CASE WHEN users.zastupce > 0 THEN \'liska\' ELSE \'nula\' END , sms.id as myID, sms.jmeno as myNAME, users.jmeno as jmeno, users.prijmeni as prijmeni FROM sms LEFT JOIN users us ON us.zalozilID = sms.zalozilID INNER JOIN users ON sms.iduzivatele = users.zastupce OR users.id = sms.iduzivatele AND (odeslano = 1 AND sms.zalozilID = 100 AND platnaSMS = 1 OR users.zastupce = sms.iduzivatele AND odeslano = 1 AND platnaSMS = 1 OR sms.iduzivatele = 100 AND odeslano = 1 AND platnaSMS = 1) GROUP BY sms.id ORDER BY sms.id DESC')
.\import.php#656: PMA_parseAnalyze(
string 'SELECT *, CASE WHEN users.zastupce > 0 THEN \'liska\' ELSE \'nula\' END , sms.id as myID, sms.jmeno as myNAME, users.jmeno as jmeno, users.prijmeni as prijmeni FROM sms LEFT JOIN users us ON us.zalozilID = sms.zalozilID INNER JOIN users ON sms.iduzivatele = users.zastupce OR users.id = sms.iduzivatele AND (odeslano = 1 AND sms.zalozilID = 100 AND platnaSMS = 1 OR users.zastupce = sms.iduzivatele AND odeslano = 1 AND platnaSMS = 1 OR sms.iduzivatele = 100 AND odeslano = 1 AND platnaSMS = 1) GROUP BY sms.id ORDER BY sms.id DESC',
Priznam sa, ze mysql moc nepoznam, ale klasicky musis grupovat podla toho, co selectujes. Skus si tvoju query pustit v nejakom sql klientovi, nech vidis poriadne chybu, backtrace co sem davas nehovori nic.
Vz5e39m to sakra na SW vrstvě a bude.
Pole vytvorene case bys mel pojmenovat
Zacni tim, ze si udelas obycejny jednoduchy select, kde vratis pres case spravny vysledek, pak to zabuduj do sveho dotazu