Upoređivanje dve tabele i filtriranje

[ nem @ 23.10.2021. 19:53 ] @

Pozdrav, već nedelju dana se mučim sa ovim i ne uspevam da napravim da radi kako treba.
Imam tabelu 'korisnici' (korisnicki_broj, prezime_ime, mesto_id, ulica_id, kucni_broj) i tabelu
prijave(korisnicki_broj, prezime_ime, mesto_id, ulica_id, kucni_broj, godina, mesec, stanje, ocitavac_id)
Do sada se ovo koristilo da korisnici uđu na portal, upišu stanje vodomera, kliknu na prijavi i to se sačuva u tabeli 'prijave'
Sada treba da napravim tabelu 'ocitavanje' koja ce biti rezultat upoređivanja tabele 'korisnici' i tabele 'prijave' - odnosno ako korisnik sam online prijave stanje vodomera da se ne pojavljuje u tabeli 'ocitavanje'.
Praktično... očitavač ide ulicom i ako je korisnik iz neke ulice npr. kućni broj 2 prijavio vodomer on se neće pojaviti na listi korisnika za očitavanje, a korisnik koji živi u broju 4 nije prijavio i on se pojavljuje na listi.
Još treba uzeti u obzir i mesec očitavanja. Jer npr. korisnik je prijavio u 9. mesecu a sada treba da radim za 10. mesec. Da li da pravim 'view' za svaki mesec?
Probao sam LEFT JOIN, NOT IN, NOT EXIST, svašta nešto i sad sam totalno zbunjen, ako može bar neki savet na šta da se fokusiram. Hvala

[ djoka_l @ 23.10.2021. 21:16 ] @

Dok čitam post, čupam ovo malo kose na glavi, a Bojs i Kod se prevrću u grobu. U tvojoj firmi nisu čuli za normalne forme?
Ovo treba staviti u neki udžbenik, pa da se na fakultetima predaje kako ne treba dizajnirati tabele.

UŽAS!

Jedan način:

Code (sql):

CREATE TABLE korisnici (id INTEGER, brojilo INTEGER);

CREATE TABLE prijave (id INTEGER, brojilo INTEGER, mesec INTEGER);

INSERT INTO korisnici VALUES( 1, 1);

INSERT INTO korisnici VALUES( 2, 2);

INSERT INTO korisnici VALUES( 3, 3);

INSERT INTO korisnici VALUES( 4, 4);

INSERT INTO prijave VALUES ( 1, 1, 1);

INSERT INTO prijave VALUES ( 1, 1, 2);

INSERT INTO prijave VALUES ( 2, 2, 1);

INSERT INTO prijave VALUES ( 4, 4, 2);

SELECT k.* FROM korisnici k

WHERE NOT EXISTS ( 

   SELECT 1 FROM prijave p

   WHERE p.id = k.id

   AND p.brojilo = k.brojilo 

   AND p.mesec = 2);

https://paiza.io/projects/PwlqBZJzyxqJo_npa_mB7Q?language=mysql

[ djoka_l @ 23.10.2021. 21:18 ] @

Uzgred, nisam ni pokušao da ti popravim dizajn, samo me mrzelo da kucam gomilu kolona...

[ nem @ 23.10.2021. 21:23 ] @

Šta je problem sa tabelama?

[ djoka_l @ 23.10.2021. 21:39 ] @

Da li me ti zezaš?
Šta je loše u tabelama, osim što u tabeli prijave imaš PET kolona koje su iste kao u korisnici?
Pa onda za svaki mesec i godinu imaš ponovljeno IME i PREZIME mesto ...
To što nemaš primarni ključ ni u jednoj tabeli?

kako bi to trebalo da izgleda
Tabela brojilo (id, pa sve ostalo što ti je bitno)
Tabela prijave (id_brojila, god_mesec, pa sve ostalo)
Tabela korisnici( id, ime_i_prezime, ...)
Tabela korisnik_brojila ( id_korisnika, id_brojila)...

https://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form

[ Predrag Supurovic @ 24.10.2021. 01:20 ] @

Citat:

djoka_l:

Code:

select k.* from korisnici k
where not exists (
   select 1 from prijave p
   where p.id = k.id
   and p.brojilo = k.brojilo
   and p.mesec = 2);

Ja ih ovo obrnuo

SELECT p.* FROM prijave WHERE p.brojilo = k.brojilo AND p.mesec = 2

Po svemu sudeći to mu daje sve podatke koji mu trebaju zato što ima redudantne podatke jer nije dobro uradjena normalizacija pa veza sa tabelom korisnici i ne treba.

A ako bas treba da se poveze, onda bi tabelu korisnici prosto povezao sa LEFT JOIN....

Pridružujem se sablažnjavanju nad strukturama tabela. :)

[ bogdan.kecman @ 24.10.2021. 07:48 ] @

@djoka, sta mislis kako su nastale sve ove nosql "baze" :D

@nem, sve je problem sa tabelama, na bilo kom ispitu iz baza bi te izbacili sa ispita, u bilo kojoj pismenom firmi bi dobio otkaz

ako zelis da razumes i da naucis nesto https://mysql.rs/tag/normalizacija/

[ VladaSu @ 24.10.2021. 19:07 ] @

Definitvno da tabele ne valjaju ali ima tu jos problema.
Cak i pored normalizacije, na starim ocitavanjima prilikom promene adrese korisnika ne sme da se menja i adresa gde je vec ocitano.
Kod promene adrese najjednostavnije je dodati novog korisnika ali tu se gubi kontinuitet u placanjima, dugovanjima, zalbama, intervencijama....
Ja bih tu dodao i tabelu "vodomeri", kako bi se pratilo stanje kada, koji i gde je vodomer ugradjen, kako bi se pratilo prethodno stanje. Posle 5 godina vodomer mora da se menja.
Ne ocitavas ti korisnika ili adresu vec vodomer. Vodomer moze da promeni vlasnika. Vlasnik moze da promeni adresu.

[ djoka_l @ 24.10.2021. 19:28 ] @

A i jedna osoba može da ima više stanova/vodomera.
Adresa za dostavu računa ne mora da bude ista kao adresa objekta na kojem se nalazi vodomer.

Vodomer može da promeni vlasnika. Stanje vodomera može da se očita više puta u toku meseca...
Gomila problema sa dizajnom.

[ Predrag Supurovic @ 24.10.2021. 19:28 ] @

Citat:

VladaSu: Definitvno da tabele ne valjaju ali ima tu jos problema.
Cak i pored normalizacije, na starim ocitavanjima prilikom promene adrese korisnika ne sme da se menja i adresa gde je vec ocitano.
Kod promene adrese najjednostavnije je dodati novog korisnika ali tu se gubi kontinuitet u placanjima, dugovanjima, zalbama, intervencijama....
Ja bih tu dodao i tabelu "vodomeri", kako bi se pratilo stanje kada, koji i gde je vodomer ugradjen, kako bi se pratilo prethodno stanje. Posle 5 godina vodomer mora da se menja.
Ne ocitavas ti korisnika ili adresu vec vodomer. Vodomer moze da promeni vlasnika. Vlasnik moze da promeni adresu.

Rekao bih da postoje sve te tabele i da takođe nisu normalizovane, nego je čovek prikazao samo one tabele koje su relevantne za ovo što je pitao. :)

[ nem @ 24.10.2021. 20:47 ] @

Hvala svima koji su pokusali da pomognu.
Primarni ključ je korisnicki_broj. Naveo sam sve kolone da bi bilo jasnije sta zelim da postignem, a ne da diskutujem o dizajnu baze, tebi djoko ako se cupa kosa zbog mojih tabela, slobodno.

[ VladaSu @ 25.10.2021. 07:21 ] @

Izgleda da ne svhatas o cemu pricamo. Tvoj problem se resava tako sto ces prvo promeniti dizajn baze i tek onda moze da se diskutuje o nekim SQL-ovima.
Vremenom ces morati da radis izvestaje i druge upite koji nisu ovako jednostavni i problem ce ti biti neresiv iz dva razloga.
Prvi razlog, mozda i resiv, pisaces u najmanju ruku cudne SQL-ovi koje niko nece znati procitati, cak ni ti kada posle vikenda nastavis da radis na kodu.
Drugi razlog koji nije resiv je taj sto ces imati gomilu pogresnih podataka u bazi i to ces tesko ispraviti. Pogresni podaci ce rezultirati pogresnim rezultatima.
Kakav god SQL da napravis nikada neces dobiti ocekivane rezultate.

Iz tvog poslednjeg odgovora mogu da zakljucim da ne znas sta je dizajn baze. Nama na forumu nije problem sto si ti naveo sve kolone.
Problem sto se te kolone nalaze u tim tabelama kod tebe.
Dizajn baze podrazumeva koje tabele se nalaze u bazi i koje kolone imaju te tabele.
Nije svejedno kako napravati te tabele i koje kolone staviti u te tabele i to je kod tebe jako pogresno.

Ako je seminarski ili ispiti onda ga neces poloziti. Ako stvarno radis posao za neki firmu onda odradi kako si zapoceo, uzmi novac i bezi i promeni broj telefona da te vise ne zovu. :)

Ovo je jos jedno resenje za 10-ti mesec ove godine.

Code (sql):

SELECT k.*, p.*

FROM korisnici k

LEFT JOIN prijave p ON p.korisnicki_broj=k.korisnicki_broj AND mesec=10 AND godina=2021

WHERE p.korisnicki_broj IS NULL

[ nem @ 25.10.2021. 07:41 ] @

Hvala Vlado na pomoći.
Ovo je samo jedan mali nezavisni portal i nemam potrebu za izveštajima i ostalim. Podaci moraju da budu ovakvi jer podatke o korisnicima dobijam iz dbf baze i rezulate prikupljanja prijave ponovo moram da ubacim u dbf bazu koja je takookreirana i tu ne mogu ništa da menjam.
Svakako ću probati da uradim pravilno dizajn baze iako praktično neću moći da koristim. Kada uradim obratiću se opet ovde ako neko bude raspoložen za dalje savete.

[ VladaSu @ 25.10.2021. 08:10 ] @

Sada je jasnije.
Posto je to mini portal i mnoge stvari nisu bitne i pretpostavljam da ti import radi neki kod, onda kod importa u tvoje tabela mogao bi samo da ih punis podacima koji ti trebaj,u a to je samo oni koji nisu ocitani.

[ bogdan.kecman @ 25.10.2021. 10:15 ] @

radis import iz DBF fajla u normalizovano bazu tako sto citas tu datu i
pravilno je ubacujes u bazu,

exportujes datu u DBF fajl tako sto poslozis datu u upitu da dobijes
izlaz kakav ti je potreban

kako cuvas datu (db model) i kako dobijes i vracas datu su 3 razlicite
stvari

prvi i osnovni princip relacionih baza podataka je da nemas duplikat
date, ako imas duplikat date data ce se koraptovati vremenom

ako zelis da zadrzis "ovakav model" onda koristi neki mongo ili slicnu
nosql kontrapciju gde je normalno da dupliciras datu (otprilike tamo
struktura izgleda identicno ovako kako si ti napravio)

[ VladaSu @ 25.10.2021. 11:08 ] @

Ne znam u kojem programu radi ali moze da procita korisnike i da procita prijave iz DBF fajla kao sto vec radi ali sa malim izmenama.
1. Procita prvo korisnike u jedan array gde ce key biti korisnicki_broj
2. Zatim procita prijave ali u array koji ce upisatvati procitane podatke upisuje samo za trazeni mesec i godinu i key ce biti korisnicki broj.
3. Ako je php uradi nesto kao array_diff_key i dobije gotov rezultat koji upise u jednu tabelu.