složitější SQL dotaz

Webdesign, HTML, CSS, Flash, PHP, ASP, .NET, JavaScript. Kritika www stránek na Smetišti.

Moderátor: Moderátoři Živě.cz

Odeslat příspěvekod maaacaCZ 17. 1. 2017 22:59

Prosím o pomoc s tímto problémem, který je nad moje síly:
Mám 4 tabulky
countries_gallery_map (country_id, gallery_id)
galleries (date, gallery_id, ...)
countries_trips_map (country_id, trip_id)
trips (date, trip_id, ...)

country_id jsou uloženy ve dvou tabulkách (countries_gallery_map a countries_trips_map) a datumy jsou v jím odpovídajích (galleries a trips).

Jde mi o to dostat seznam country_id seřazený od nejstaršího date, kde ale bude každé country_id jen jedno (to nejstarší aniž by záleželo na tom jestli bude patřit k tabulce trips nebo galleries).

Doufám, že je to srozumitelné, případné nejasnosti upřesním.
maaacaCZ
Kolemjdoucí

Odeslat příspěvekod Bespi_ 17. 1. 2017 23:48

Nastudovat spojovani tabulek (JOIN) a jeho ruzne typy. Pro serazeni pak ORDER BY. Jinak dana vec neni slozitejsi SQL dotaz, ale uplny zaklad :-) .
Bespi_
Junior

Odeslat příspěvekod Just_jo 17. 1. 2017 23:48

Když ponecháme stranou špatně navrženou DB strukturu, tak by se hodilo napsat i o jaký SQL server jde.
Just_jo
Junior
Uživatelský avatar

Odeslat příspěvekod maaacaCZ 18. 1. 2017 09:28

Struktura je taková jaká je. Teď bych to asi udělal jinak, ale holt chybama se člověk učí, navíc na těch tabulkách jsou postavené další věci, které tady nevidíte.
Na mě je ten dotaz už složitější, jsem jen samoukamatér. Zvládnul jsem postavit dotazy zvlášť pro tripy a galerie, ale nepodařilo se mi to dát dohromady tak, aby byl vzat v úvahu jen ten nejstarší záznam pro tu zemi.
Jo a jedná se o MySQL.
maaacaCZ
Kolemjdoucí

Odeslat příspěvekod Nargon 18. 1. 2017 10:31

Píšu to z hlavy, tak tam asi budou nějaké syntaktické chyby, tak si je kdyžtak oprav. Ale podle mě by mohlo fungovat tohle:
Kód: Vybrat vše
SELECT both.country_id AS Country, MIN(both.date) AS OldestDate
FROM     (
         SELECT country_id, date FROM countries_gallery_map JOIN galleries ON countries_gallery_map.gallery_id = galleries.gallery_id
         UNION ALL
         SELECT country_id, date FROM countries_trips_map JOIN trips ON countries_trips_map.trip_id = trips.trip_id
         ) as both
GROUP BY both.country_id
ORDER BY OldestDate;

Určitě to jde napsat i jinak, ale myslím si že tohle je pěkně pochopitelný a hned pochopíš jak to funguje. Jen asi ta složitost dotazu nebude nejnižší :)
Desktop: Ryzen 7 1800X (3.95GHz, 1.35V), Asus Crosshair VI Hero, 16GB DDR4 Ram (3200MHz), 128GB SSD + 3TB HDD, Nvidia GTX 1080
Notebook: Asus UL50VT 15.6" (SU7300@1.7GHz, 4GB ram, 500GB HDD, Intel GMA 4500MHD + nVidia G210M, dlouha vydrz cca 7+ hod)
Nargon
Moderátor

Odeslat příspěvekod powlcz 18. 1. 2017 14:16

struktura je vážně divná.

Kód: Vybrat vše
select
      q.country_id,
      max(q.date) date
   from
   (
         select
               gmap.country_id,
               max(g.date) date
            from countries_gallery_map gmap
               inner join galleries g
                  on g.id = gmap.gallery_id
            group by
               gmap.country_id
      union all
         select
               tmap.country_id,
               max(t.date) date
            from countries_trips_map tmap
               inner join trips t
                  on t.id = tmap.gallery_id
            group by
               tmap.country_id
   )q
      group by
         q.country_id
      order by
         q.date desc

Chronický Ujížděč na nevhodnostech
powlcz
Junior

Odeslat příspěvekod maaacaCZ 18. 1. 2017 18:08

Děkuji oběma.

Zkusil jsem ten dotaz od Nargona, protože mi přijde vcelku srozumitelný. Zadal jsem ho do phpmyadmineru a skončil jsem na téhle chybě:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'both GROUP BY both.country_id ORDER BY OldestDate' at line 6

Je to pro mne vyšší dívčí, takže netuším v čem je problém. Tady jsou informace o verzi MySQL, kterou používám.
maaacaCZ
Kolemjdoucí

Odeslat příspěvekod Nargon 18. 1. 2017 18:43

Tak vyzkoušej ještě to druhé co tu psal powlcz, v principu je to asi stejné jako to co jsem napsal já, ikdyž se mi tam nezdají funkce max() u toho datumu, podle mě by mělo být správně min(), ale to zjistíš po prvním spuštění, co je správně.

Já jsem si to teď tak na rychlo vyzkoušel a korektně se to zpracovalo a i jsem dostal výsledky. Dokonce vypadají správně :) Ale zkoušel jsem to na MSSQL, které tu mám nainstalované.
Nevím co se MySQL nelíbí. Možná ten středník. A nebo něco úplně jiného. Buh ví. Mě totiž SQL Management Studio obarvilo slůvko "date" jako vyhrazené slovo, ikdyž je to název sloupce. Ovšem to snad problém není.
Desktop: Ryzen 7 1800X (3.95GHz, 1.35V), Asus Crosshair VI Hero, 16GB DDR4 Ram (3200MHz), 128GB SSD + 3TB HDD, Nvidia GTX 1080
Notebook: Asus UL50VT 15.6" (SU7300@1.7GHz, 4GB ram, 500GB HDD, Intel GMA 4500MHD + nVidia G210M, dlouha vydrz cca 7+ hod)
Nargon
Moderátor

Odeslat příspěvekod maaacaCZ 18. 1. 2017 20:33

Tak po asi hodině backtickování se my to podařilo rozchodit :-D

Ještě mě napadlo, jestli by k tomu nešel připsat dotaz do tabulky countries, kde bych podle both.country_id vybralo country_name (k výsledku stávajícího dotazu by se přidal další sloupeček s plným jménem země).

S tou strukturou máte pravdu, prase aby se v tom vyznalo, příště se polepším!
maaacaCZ
Kolemjdoucí

Odeslat příspěvekod Nargon 18. 1. 2017 21:13

To by mělo jít taky. Cca takto:
Kód: Vybrat vše
SELECT both.country_id AS CountryID, MIN(both.date) AS OldestDate, countries.country_name as CountryName
FROM     (
         SELECT country_id, date FROM countries_gallery_map JOIN galleries ON countries_gallery_map.gallery_id = galleries.gallery_id
         UNION ALL
         SELECT country_id, date FROM countries_trips_map JOIN trips ON countries_trips_map.trip_id = trips.trip_id
         ) as both
LEFT JOIN countries ON both.country_id = contries.country_id
GROUP BY both.country_id
ORDER BY OldestDate

A jen tak ze zvědavosti, co tomu nakonec bylo? tj jak si to musel upravit aby ti to MySQL sežralo a vyplivlo výsledek?
Desktop: Ryzen 7 1800X (3.95GHz, 1.35V), Asus Crosshair VI Hero, 16GB DDR4 Ram (3200MHz), 128GB SSD + 3TB HDD, Nvidia GTX 1080
Notebook: Asus UL50VT 15.6" (SU7300@1.7GHz, 4GB ram, 500GB HDD, Intel GMA 4500MHD + nVidia G210M, dlouha vydrz cca 7+ hod)
Nargon
Moderátor

Odeslat příspěvekod maaacaCZ 18. 1. 2017 21:28

Super, to jsem skoro dal sám, jen jsem nepoužil ten LEFT JOIN.

Musel jsem použít pár backticků a taky jsem měl trochu bordel ve struktuře. Tady je finální a u mne funkční dotaz:
Kód: Vybrat vše
SELECT both.country_id AS Country, MIN(both.date) AS OldestDate, countries.country_name AS CountryName
FROM     (
         SELECT `country_id`, `date` FROM `countries_gallery_map` JOIN `galleries` ON (countries_gallery_map.`gallery_id` = galleries.`gallery-id`)
         UNION ALL
         SELECT `country_id`, `date` FROM `countries_trips_map` JOIN `trips` ON (countries_trips_map.`trip_id` = trips.`tripid`)
         ) as `both`
LEFT JOIN countries ON both.country_id = countries.country_id         
GROUP BY both.country_id
ORDER BY OldestDate


Každopádně díky za ochotu pomoc!
maaacaCZ
Kolemjdoucí

Odeslat příspěvekod Nargon 18. 1. 2017 21:51

Tak ono je skoro jedno zda použiješ JOIN nebo LEFT JOIN. Předpokládám že máš korektně vyplněná data, tj že všechny country_id mají odpovídající název. Pak to vrací stejné výsledky. Ale pro případ že by jsi pro nějaké ID neměl vyplněný název tak se hodí použít LEFT (eventuelně RIGHT) JOIN aby jsi měl alespoň výsledek bez názvu země.
Desktop: Ryzen 7 1800X (3.95GHz, 1.35V), Asus Crosshair VI Hero, 16GB DDR4 Ram (3200MHz), 128GB SSD + 3TB HDD, Nvidia GTX 1080
Notebook: Asus UL50VT 15.6" (SU7300@1.7GHz, 4GB ram, 500GB HDD, Intel GMA 4500MHD + nVidia G210M, dlouha vydrz cca 7+ hod)
Nargon
Moderátor

Odeslat příspěvekod powlcz 19. 1. 2017 14:47

jasně, já to query psal na MariaDB nebo MSSQL, ale v principu mají stejnou logiku. Příště zkus tázat dotaz jako na stackoverflow - návod http://meta.stackoverflow.com/questions ... ion/271056

Tedy tabulky znázorněny třeba skrze https://senseful.github.io/web-tools/text-table/ apod :). My spíš poskytli "example" a čekali jsme, že se z toho naučíš group by, union, joiny a vnořené selecty; a query si poskládáš sám
Chronický Ujížděč na nevhodnostech
powlcz
Junior

Odeslat příspěvekod worrapS 20. 1. 2017 20:54

Ty zpětné uvozovky tam mají jaký význam? :shock:
Pracovat s počítači je docela otrava. Vůbec, pracovat je otrava. Možná to nakonec s těmi počítači nebude až tak zlé.
worrapS
Junior
Uživatelský avatar


Kdo je online

Uživatelé procházející toto fórum: Žádní registrovaní uživatelé a 0 návštevníků