Cześć,
używam sql opartego na tym temacie (dziękuję @Panczo)
https://4programmers.net/Forum/Bazy_danych/327930-tabela_z_wieksza_iloscia_niz_16_pol_projetowanie_bazy?page=1
SQL wygląda tak:
SELECT DISTINCTROW t2.*
FROM tblvolumes AS t2
LEFT JOIN qryim_sourcevolumes AS t1
ON ( Nz(t2.linuxmountpassno, "null") =
Nz(t1.linuxmountpassno, "null") )
AND ( Nz(t2.linuxmountfreq, "null") =
Nz(t1.linuxmountfreq, "null") )
AND
( Nz(t2.mountoptions, "null") = Nz(t1.mountoptions, "null") )
AND ( Nz(t2.lvmstripesize, "null") =
Nz(t1.lvmstripesize, "null") )
AND ( Nz(t2.volumegroup, "null") = Nz(t1.volumegroup, "null") )
AND ( Nz(t2.providerid, "null") = Nz(t1.providerid, "null") )
AND ( Nz(t2.volumeid, "null") = Nz(t1.volumeid, "null") )
AND ( Nz(t2.earlymount, "null") = Nz(t1.earlymount, "null") )
AND
( Nz(t2.servicelevel, "null") = Nz(t1.servicelevel, "null") )
AND ( Nz(t2.providerfulfillment, "null") =
Nz(t1.providerfulfillment, "null") )
AND ( Nz(t2.consumerfulfillment, "null") =
Nz(t1.consumerfulfillment, "null") )
AND ( Nz(t2.writeacceleratorenabled, "null") =
Nz(t1.writeacceleratorenabled, "null") )
AND ( Nz(t2.caching, "null") = Nz(t1.caching, "null") )
AND ( Nz(t2.function, "null") = Nz(t1.function, "null") )
AND ( Nz(t2.description, "null") = Nz(t1.description, "null") )
AND ( Nz(t2.formatcommand, "null") =
Nz(t1.formatcommand, "null") )
AND ( Nz(t2.sharemode, "null") = Nz(t1.sharemode, "null") )
AND
( Nz(t2.percentsnapshotspace, "null") =
Nz(t1.percentsnapshotspace, "null") )
AND ( Nz(t2.filesystemtype, "null") =
Nz(t1.filesystemtype, "null") )
AND ( Nz(t2.disknumber, "null") = Nz(t1.disknumber, "null") )
AND ( Nz(t2.physicaldiskmodelidfk, "null") =
Nz(t1.physicaldiskmodelidfk, "null") )
AND ( Nz(t2.tierid, "null") = Nz(t1.tierid, "null") )
AND ( Nz(t2.volumenameidfk, "null") =
Nz(t1.volumenameidfk, "null") )
AND ( Nz(t2.environmentid, "null") =
Nz(t1.environmentid, "null") )
AND ( Nz(t2.ordinal, "null") = Nz(t1.ordinal, "null") )
WHERE t1.ordinal IS NULL
AND t1.environmentid IS NULL
AND t1.volumenameidfk IS NULL
AND t1.tierid IS NULL
AND t1.physicaldiskmodelidfk IS NULL
AND t1.disknumber IS NULL
AND t1.filesystemtype IS NULL
AND t1.percentsnapshotspace IS NULL
AND t1.sharemode IS NULL
AND t1.subjectcomponent IS NULL
AND t1.formatcommand IS NULL
AND t1.description IS NULL
AND t1.function IS NULL
AND t1.caching IS NULL
AND t1.writeacceleratorenabled IS NULL
AND t1.consumerfulfillment IS NULL
AND t1.providerfulfillment IS NULL
AND t1.servicelevel IS NULL
AND t1.earlymount IS NULL
AND t1.volumeid IS NULL
AND t1.providerid IS NULL
AND t1.volumegroup IS NULL
AND t1.lvmstripesize IS NULL
AND t1.mountoptions IS NULL
AND t1.linuxmountfreq IS NULL
AND t1.linuxmountpassno IS NULL;
i działa. Kiedy mam nulle w jakims polu i robie joina to jest to zauwazane przez Access.
Sprawdziłem, kiedy usune jeden wiersz z 1 tabeli to uzywajac tego sqla zobaczę.
Problem mam za to na przykladzie poniżej:
SELECT t2.VolumeName
FROM Temp_VolumeChanges AS t2 LEFT JOIN tblVolumeNames AS t1 ON Nz(t2.VolumeName,"Null") = Nz(t1.VolumeName,"Null")
WHERE t1.VolumeName Is Null;
Ktory pokazuje:
https://ibb.co/Db0XvRm
Dlaczego?
Co sie z tym dzieje?
Tabele wyglada tak:
tblVolumeNames
VolumeNameID VolumeName
1 Vol1
2 Vol2
3 Null (blank)
4 Vol3
Temp_VolumeChanges
ID VolumeName Name
1 Vol1 Paul
2 Null (blank) Jacek
3 Null (blank) John
Dodałem w załączeniu przykladowa baze danych.
Nie rozumiem dlaczego raz to dziala a drugi raz nie.
Czy to sie bedzie liczylo tylko dla wszystkich pól?
Jak napisać poprawnego sql'a w Accesie? (Bez Nz tez nie działa).
Prosze pomóżcie
Pozdrowienia,
Jacek