MySQL - Pobieranie danych z wielu tabel

Witajcie, mam następujący problem z którym nijak nie wiem jak sobie poradzić ;/

Mam tabele 'produkty' a w niej
id, nazwa, ean, kategoria

gdzie kategoria jest typu int ( identyfikator kategorii)

do tego jest tabela kategorie
id, nazwa (id odpowiada 'kategoria' z tebeli produkty)

do tego jest jeszcze tabela ilosc

id, ilosc (id odpowiada id produktu ilość to info ile danego produktu jest)

chciałbym wyciągnąć z bazy danych liste produktów z podziałem na kategorie i ilością produktów przykładowo dla tabel:

produkty

kategorie

ilosc_1

po wywołaniu zapytania chciałbym otrzymać wynik

Umiem połączyć 2 tabele tak żeby wyświetlało mi dane produktu i jego ilośc ale nie wiem jak zrobić aby w wyniku zapytania zamiast cyfry odpowiadającej kategorii pojawiała się sama kategoria;/

obecnie moje zapytanie wygląda następujaco:

SELECT i.id, i.nazwa, i.ean, m.ilosc FROM items i JOIN ilosc_1 m USING(id);

ilości są trzymane w osobnej tabeli z tego względu że produkty w każdym magazynie są takie same natomiast ilość danego produktu w danym magazynie jest różna

Jakieś propozycje jak rozwiązać ten problem? na co powinienem zwrócić uwagę ewentualnie co robię źle?

Nasunęło mi się kolejne pytanie co do owych tabel ale pod kontem optymalizacji

Wiedząc że produkty się nie zmieniają (przeglądając tabele z różnych magazynów) a zmienia się jedynie ilość danego produktu czy nie lepiej byłoby wygenerować plik z samą zawartością tabeli produkty (z podpiętymi kategoriami) i pytać tylko o ilości? jak to potem połączyć? i czy takie coś jest możliwe a przede wszystki bardziej optymalne?
Wiadomo, przy dodaniu nowego produktu taki plik kasujemy i generujemy przy pierwszej możliwej okazji ale każda następna osoba już nie musi czekać na wykonanie zapytania sql a jedynie na wczytanie pliku... czy to się opłaca?

Dla ścisłości ilość kategorii produktów liczymy w setkach(200-300) ilość samych produktów może dojść do tysięcy a ilość magazynów to około 20

to są podstawy SQLa.

SELECT i.id, k.nazwa AS kategoria, i.nazwa, i.ean, m.ilosc FROM items AS i LEFT JOIN ilosc_1 AS m ON i.id = m.id LEFT JOIN kategorie AS k ON i.kategoria = k.id

BTW nazywanie w każdej tabeli pola kluczowego id to nie jest zbyt dobry pomysł. Lepiej tworzyć nazwy przez dodanie na końcu do nazwy tabeli _id, czyli np dla tabeli kategorie będziesz miał kategoria_id i w każdej tabeli, gdzie będzie referencja do tego pola nazywać go też tak samo. Przy takiej nomenklaturze od razu widać co z czym jest połączone.
No i mam nadzieję, że ilosc_1 to nie jest tabela z pierwszym magazynem, a ilosc_20 z dwudziestym

Misiekd napisał(a)

to są podstawy SQLa.
BTW nazywanie w każdej tabeli pola kluczowego id to nie jest zbyt dobry pomysł. Lepiej tworzyć nazwy przez dodanie na końcu do nazwy tabeli _id, czyli np dla tabeli kategorie będziesz miał kategoria_id i w każdej tabeli, gdzie będzie referencja do tego pola nazywać go też tak samo. Przy takiej nomenklaturze od razu widać co z czym jest połączone.

Dzięki za wskazówkę - poprawiłem i wiedza na przyszłość została :)

Misiekd napisał(a)

No i mam nadzieję, że ilosc_1 to nie jest tabela z pierwszym magazynem, a ilosc_20 z dwudziestym

Dokładnie

Wiem że to podstawy ale nie potrafiłem poukładać sobie odpowiednich połączań żeby wszystko działało - na co dzień korzystam z prostszych konstrukcji i brak mi nieco doświadczenia niestety, Mam jeszcze pytanie co do warunkowania zapytania - tak dla pewności. Czy bezpośrednio po:

SELECT i.id, k.nazwa AS kategoria, i.nazwa, i.ean, m.ilosc FROM items AS i LEFT JOIN ilosc_1 AS m ON i.id = m.id LEFT JOIN kategorie AS k ON i.kategoria = k.id

Mogę dodać dodatkowe warunki ? LIMIT, WHERE itp czy jakoś specyficznie się to robi?

No i to o co pytałem jeszcze w pierwszym poście - czy takie rozwiązanie jest optymalne czy może da się to zrobić lepiej?

Serdeczne dzięki za pomoc, zaraz zastosuje w praktyce!

brodka napisał(a)

Misiekd napisał(a)

No i mam nadzieję, że ilosc_1 to nie jest tabela z pierwszym magazynem, a ilosc_20 z dwudziestym

Dokładnie
ech. Nie robi się tak. Przecież to są dokładnie te same dane. A jak Ci przyjdzie dodać 21-szy magazyn to będziesz zmieniał cały program, żeby go brał pod uwagę? Robi się jedną tabelę z dodatkową kolumną id_magazynu i odpowiednim indeksem do wyszukiwania towaru na konkretnym magazynie - np. po dwóch polach id_magazynu i id_towaru

Wiem że to podstawy ale nie potrafiłem poukładać sobie odpowiednich połączań żeby wszystko działało - na co dzień korzystam z prostszych konstrukcji i brak mi nieco doświadczenia niestety, Mam jeszcze pytanie co do warunkowania zapytania - tak dla pewności. Czy bezpośrednio po:

SELECT i.id, k.nazwa AS kategoria, i.nazwa, i.ean, m.ilosc FROM items AS i LEFT JOIN ilosc_1 AS m ON i.id = m.id LEFT JOIN kategorie AS k ON i.kategoria = k.id

Mogę dodać dodatkowe warunki ? LIMIT, WHERE itp czy jakoś specyficznie się to robi?
nie wiem jak z LIMIT bo baza, z którą pracuję na co dzień nie ma takiego czegoś, ale z WHERE, 'ORDER BY, GROUP BY, HAVING` tak to właśnie działa, że dodaje się je jako kolejne elementy zapytania

No i to o co pytałem jeszcze w pierwszym poście - czy takie rozwiązanie jest optymalne czy może da się to zrobić lepiej?

Przede wszystkim to co Ci napisałem odnośnie magazynów.

Misiekd napisał(a)

brodka napisał(a)

Misiekd napisał(a)

No i mam nadzieję, że ilosc_1 to nie jest tabela z pierwszym magazynem, a ilosc_20 z dwudziestym

Dokładnie
ech. Nie robi się tak. Przecież to są dokładnie te same dane. A jak Ci przyjdzie dodać 21-szy magazyn to będziesz zmieniał cały program, żeby go brał pod uwagę? Robi się jedną tabelę z dodatkową kolumną id_magazynu i odpowiednim indeksem do wyszukiwania towaru na konkretnym magazynie - np. po dwóch polach id_magazynu i id_towaru

U mnie to wygląda tak że mam dodatkową tabele z info o magazynach i tam trzymam identyfikatory magazynu - zrobiłem tak bo wydawało mi się że niepotrzebnie bym zamulał serwer robiąc to w inny sposób

W twoim rozwiązaniu tabela z towarami byłaby spora - 20-40 tysięcy towarów mnożone przez ilość magazynów (wstępnie około 17) z czego standardowy użytkownik potrzebuje danych tylko z jednego magazynu a towary są wyszukiwane asynchronicznie więc baza dostaje zapytania non stop (user wpisuje kolejną literkę nazwy a całe zapytanie jest wykonywane ponownie, sortuje dane kolejne zapytanie itp a wszystko na setkach tysięcy wpisów) Czy przy takich ilościach opłaca się wszystko trzymać w jednej tabeli ? Czy nie optymalnie jest wyszukiwać i sortować nieco mniejsze tabele? Przede wszystkim zależy mi na tym żeby użytkownik nie musiał czekać nie wiadomo ile na np sortowanie...

powiem tak - dobrze napisany system w takim wypadku po każdej literce pobierze tylko tyle rekordów ile jest w stanie wyświetlić na raz i ani jednego więcej (w ostateczności może to być jakaś okrągła liczba, np. 50). Następnie dobrze napisany system będzie miał index na polu, po którym następuje wyszukiwanie. Co do pierwszego to dobre komponenty BD załatwią to same, co do drugiego to to się samo nie zrobi ale powinno być jako priorytet. Idąc dalej to niecały 1kk rekordów dla bazy danych na w miarę przyzwoitym sprzęcie to jest pikuś. Natomiast łatwość w dostępie do danych i elastyczność jaką daje nie rozbijanie jednej logicznej całości na x tabel nie można przecenić

No ok, dzięki za info - jeszcze to przemyśle i możliwe że poprawie (najprawodpodobnije) ale na dzisiaj musiałbym jeszcze wiedzieć jak połączyć rekordy z 10 tabel (te zakichane magazyny żeby wszystko wyświetlało mi w jednej i cholera generuje mi skrypt zapytanie i niby wszystko ok ale mam zamiast ilości sztuk na magazynie null

SELECT SQL_CALC_FOUND_ROWS i.id, k.category, i.name, i.description, i.symbol, i.ean, i.price_b, m1.number, m2.number, m3.number, m4.number, m5.number, m6.number, m7.number, m8.number, m9.number, m10.number, m11.number, m12.number, m13.number, m14.number, m15.number, m16.number, i.unit                  
                   FROM items AS i
                   LEFT OUTER JOIN magazyn_1 AS m1 ON i.id = m1.id 
                   LEFT OUTER JOIN magazyn_2 AS m2 ON i.id = m2.id 
                   LEFT OUTER JOIN magazyn_3 AS m3 ON i.id = m3.id 
                   LEFT OUTER JOIN magazyn_4 AS m4 ON i.id = m4.id 
                   LEFT OUTER JOIN magazyn_5 AS m5 ON i.id = m5.id 
                   LEFT OUTER JOIN magazyn_6 AS m6 ON i.id = m6.id 
                   LEFT OUTER JOIN magazyn_7 AS m7 ON i.id = m7.id 
                   LEFT OUTER JOIN magazyn_8 AS m8 ON i.id = m8.id 
                   LEFT OUTER JOIN magazyn_9 AS m9 ON i.id = m9.id 
                   LEFT OUTER JOIN magazyn_10 AS m10 ON i.id = m10.id 
                   LEFT OUTER JOIN magazyn_11 AS m11 ON i.id = m11.id 
                   LEFT OUTER JOIN magazyn_12 AS m12 ON i.id = m12.id 
                   LEFT OUTER JOIN magazyn_13 AS m13 ON i.id = m13.id 
                   LEFT OUTER JOIN magazyn_14 AS m14 ON i.id = m14.id 
                   LEFT OUTER JOIN magazyn_15 AS m15 ON i.id = m15.id 
                   LEFT OUTER JOIN magazyn_16 AS m16 ON i.id = m16.id 
                   LEFT OUTER JOIN kategorie_id AS k ON i.category_id = k.id
                   ORDER BY  m1.numberasc
                    LIMIT 0, 10

To już jest chyba setna wersja - chwytam się już losowych komend bo cholery dostaje - pewnie mnie za to wyśmiejesz ale już nawet nie pamiętam w jaki sposób to robiłem więc daje ostatnią wersje...

Co do indeksu na polu wedle którego szukam - szukanie jest wykonywane na kilku polach (wpisujesz w jednym miejscu a w wyszukiwaniu uwzględnia wyniki z kilku - jeśli szukany wyraz jest choć w jednym to go pokazuje) indeksy dać na wszystkie pola? chyba bez sensu?
Skrypt pobiera tyle ile potrzebuje i tyle wyświetla - chodziło mi tu bardziej o to czy szybciej będzie wyniki do wyświetlenia wyszukać w dużej tabeli (Twoja propozycja) czy w małej... Twoje rozwiązanie wydaje się mi ciekawym ale na razie muszę to zrobić niestety po swojemu - za dużo roboty by było teraz z poprawianiem (muszę pokazać demo działającej apki) ale w edycji finalnej myślę że zrobię tak jak radzisz

Czy ktoś ma pomysł jak sformułować zapytanie aby z tabeli o strukturze

zrobić tabele która się prezentuje w taki oto sposób:

Próbowałem z union, z left join i jakoś mi nie wychodzi;/

SELECT id_produktu, sum(case when id_magazynu=1 then ilosc else 0 end) ilosc_w_magazynie_1, sum(case when id_magazynu=2 then ilosc else 0 end) ilosc_w_magazynie_2, sum(case when id_magazynu=3 then ilosc else 0 end) ilosc_w_magazynie_3 FROM tabela GROUP BY id_produktu

brodka napisał(a)

No ok, dzięki za info - jeszcze to przemyśle i możliwe że poprawie (najprawodpodobnije)
i to jest właśnie największy babol przy rozbijaniu jednej logicznej tabelki na kilka takich samych :p

ale na dzisiaj musiałbym jeszcze wiedzieć jak połączyć rekordy z 10 tabel (te zakichane magazyny żeby wszystko wyświetlało mi w jednej i cholera generuje mi skrypt zapytanie i niby wszystko ok ale mam zamiast ilości sztuk na magazynie null
[...]
musiał byś napisać co to ma zwracać bo to zapytanie które wkleiłeś to chyba nie do końca tak ma być.

Co do indeksu na polu wedle którego szukam - szukanie jest wykonywane na kilku polach (wpisujesz w jednym miejscu a w wyszukiwaniu uwzględnia wyniki z kilku - jeśli szukany wyraz jest choć w jednym to go pokazuje) indeksy dać na wszystkie pola? chyba bez sensu?
jeśli szukasz tekstu zawsze od początku w każdym polu to dodaj osobne indeksy na każde pole, po którym szukasz i zobacz co pokaże Ci query plan - powinien skorzystać z indeksów. Dodatkowo jeśli przy szukaniu nie uwzględniasz wielkości liter to załóż indeks funkcyjny. A nie mysql takiego cuda nie ma :D

Skrypt pobiera tyle ile potrzebuje i tyle wyświetla - chodziło mi tu bardziej o to czy szybciej będzie wyniki do wyświetlenia wyszukać w dużej tabeli (Twoja propozycja) czy w małej... Twoje rozwiązanie wydaje się mi ciekawym ale na razie muszę to zrobić niestety po swojemu - za dużo roboty by było teraz z poprawianiem (muszę pokazać demo działającej apki) ale w edycji finalnej myślę że zrobię tak jak radzisz
jeśli są odpowiednie indeksy założone i dane są na tyle różne, że baza z tych indeksów skorzysta to tak na prawdę nie będziesz widział różnicy