SQL Alăturarea a 4 tabele (Programare, Sql, Bază De Date, A Se Alătura)

Daywalker a intrebat.

Trebuie să mă ocup de o bază de date veche de la departamentul companiei mele. folosim această bază de date pentru gestionarea hardware-ului, atribuirea și urmărirea.

Sunt pe cale să construiesc un nou fronted în c#, deoarece actualul MS Access devine mult prea lent pentru această sarcină.

(Am tradus numele tabelelor și al rândurilor în engleză, pentru o mai bună înțelegere)

  • tbl_hardware
  • tbl_hardware_assignment
  • tbl_accounts
  • tbl_type_bradmodel (nu este important pentru moment, și/sau se explică de la sine)

tbl_hardwareconține coloanele HW_ID, serialnumber, type,model,brand, etc. (alte informații necesare despre hardware).

tbl_hardware_assignmentconține coloanele ID, HW_ID (care se potrivește cu tbl_hardware.ID), nameID (care se potrivește cu tbl_accounts.PersID) și since (o valoare int formată din data [AAAALLZZ] când a fost creată intrarea (nu a fost ideea mea…)).

tbl_accountconține coloanele PersID, Login, etc. (alte informații interne)

Aceasta este declarația mea SQL actuală

SELECT tbl_hardware.HW_ID,
       tbl_hardware.Aktiv,
       tbl_hardware.typebradmodelID,
       tbl_type.tabel AS Type,
       tbl_brand.tabel AS Brand,
       tbl_model.tabel AS Model,
       tbl_accounts.Login,
       tbl_hardware_assignment.since
FROM tbl_hardware
LEFT OUTER JOIN tbl_typebradmodel ON tbl_hardware.typebradmodelID = tbl_typebradmodel.typebradmodelID
LEFT OUTER JOIN tbl_type ON tbl_typebradmodel.TypID = tbl_type.TypID
LEFT OUTER JOIN tbl_brand ON tbl_typebradmodel.MarkeID = tbl_brand.MarkeID
LEFT OUTER JOIN tbl_model ON tbl_typebradmodel.ModelID = tbl_model.ModelID
LEFT OUTER JOIN tbl_hardware_assignment ON tbl_hardware.HW_ID = tbl_hardware_assignment.HW_ID
LEFT OUTER JOIN tbl_accounts ON tbl_hardware_assignment.namenID = tbl_accounts.PersID
WHERE tbl_hardware.Aktiv = 1 AND 
      tbl_hardware.typebradmodelID in 
      (SELECT tbl_typebradmodel.typebradmodelID
       FROM tbl_typebradmodel
       LEFT OUTER JOIN tbl_type ON tbl_typebradmodel.TypID = tbl_type.TypID
       LEFT OUTER JOIN tbl_brand ON tbl_typebradmodel.MarkeID = tbl_brand.MarkeID
       LEFT OUTER JOIN tbl_model ON tbl_typebradmodel.ModelID = tbl_model.ModelID
       WHERE tbl_typebradmodel.MarkeID = 
             (SELECT tbl_brand.MarkeID 
              FROM tbl_brand 
              WHERE tbl_brand.tabel LIKE 'Samsung')
      ) AND 
      tbl_hardware.HW_ID in 
      (SELECT tbl_hardware_assignment.HW_ID 
       FROM tbl_hardware_assignment, 
            (SELECT MAX(tbl_hardware_assignment.since) AS lastchange,
                    tbl_hardware_assignment.HW_ID
             FROM tbl_hardware_assignment 
             GROUP BY tbl_hardware_assignment.HW_ID) lastentry
       WHERE tbl_hardware_assignment.namenID = 
             (SELECT tbl_accounts.PersID 
              FROM tbl_accounts 
              WHERE tbl_accounts.Login = 'MY_USERNAME') AND 
             tbl_hardware_assignment.HW_ID = lastentry.HW_ID AND
             tbl_hardware_assignment.since = lastentry.lastchange
      )

REZULTAT:

9778    1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER1   20100218
9778    1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER2   20100218
10497   1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER3   20100810
10498   1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER3   20100810
10498   1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER4   20100819
10497   1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER4   20100819
10497   1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   MY_USERNAME 20120601
10498   1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   MY_USERNAME 20120601
9778    1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER3   20130502
9778    1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER5   20130507
9778    1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   USER3   20130619
9778    1   2868    Monitor 24" TFT Samsung SyncMaster 2494HM   MY_USERNAME 20130725

Dar obțin prea multe rezultate, ȘI asocieri greșite/multiple de utilizatori cu un singur hardware.

Aveți vreo idee unde este greșeala mea?

BTW: Doar această instrucțiune returnează valorile corecte

SELECT tbl_hardware_assignment.HW_ID 
FROM tbl_hardware_assignment, 
     (SELECT MAX(tbl_hardware_assignment.since) AS lastchange, 
             tbl_hardware_assignment.HW_ID
      FROM tbl_hardware_assignment 
      GROUP BY tbl_hardware_assignment.HW_ID) lastentry
WHERE tbl_hardware_assignment.namenID = 
      (SELECT tbl_accounts.PersID 
       FROM tbl_accounts 
       WHERE tbl_accounts.Login = 'MY_USERNAME') AND 
      tbl_hardware_assignment.HW_ID = lastentry.HW_ID AND 
      tbl_hardware_assignment.since = lastentry.lastchange

REZULTAT:

10497   20120601
10498   20120601
11554   20120601
12353   20120601
13665   20120918
13196   20121129
14616   20130701
15073   20130705
9778    20130725

(Deoarece nu ar trebui să port lucrurile companiei în afara biroului, sper că nu am încurcat niciun rezultat sau declarații SQL).

Iată mai multe exemple de rezultate PASTEBIN

Comentarii

  • „to many results, AND wrong/multiple user mappings with one hardware” nu descrie problema suficient pentru ca cineva să înțeleagă cu adevărat ce se întâmplă. Puteți să furnizați câteva exemple (mici) de date? –  > Por lc..
  • Îmi pare rău, acum am adăugat câteva rezultate –  > Por Daywalker.
3 răspunsuri
Caleth

Nu este nevoie să unești subinterogările înapoi la tabelele din care provin și poți face JOIN direct pe ele.

Mai degrabă decât să JOINING o grămadă de tabele direct, ați putea să vă uitați la formarea de subinterogări care să obțină părțile constitutive corecte

Ceva de genul celor de mai jos ar putea fi ceea ce căutați:

SELECT tbl_hardware.HW_ID,
       tbl_hardware.Aktiv,
       tbl_hardware.typebradmodelID,
       typebradmodel.Type,
       typebradmodel.Brand,
       typebradmodel.Model,
       lastentry.Login,
       lastentry.since
FROM (SELECT
        tbl_typebradmodel.typebradmodelID,
        tbl_type.tabel AS Type,
        tbl_brand.tabel AS Brand,
        tbl_model.tabel AS Model
    FROM tbl_typebradmodel
    LEFT OUTER JOIN tbl_type ON tbl_typebradmodel.TypID = tbl_type.TypID
    LEFT OUTER JOIN tbl_brand ON tbl_typebradmodel.MarkeID = tbl_brand.MarkeID
    LEFT OUTER JOIN tbl_model ON tbl_typebradmodel.ModelID = tbl_model.ModelID
    ) typebradmodel
LEFT JOIN tbl_hardware ON tbl_hardware.typebradmodelID = typebradmodel.typebradmodelID
LEFT JOIN      
    (SELECT 
        MAX(tbl_hardware_assignment.since) AS lastchange, 
        tbl_hardware_assignment.HW_ID,
        tbl_accounts.Login
    FROM tbl_hardware_assignment
    LEFT OUTER JOIN tbl_accounts ON tbl_hardware_assignment.namenID = tbl_accounts.PersID
    GROUP BY tbl_hardware_assignment.HW_ID,tbl_accounts.Login ) lastentry ON tbl_hardware.HW_ID = lastentry.HW_ID
WHERE tbl_hardware.Aktiv = 1 AND 
    typebradmodel.Brand LIKE 'Samsung' AND
    lastentry.Login = 'MY_USERNAME'

ActualizarePartea critică aici este obținerea corectă a subinterogării lastchange, adică utilizarea tuturor coloanelor care descriu relația dintre tbl_hardware_assignment și tbl_accounts.

SELECT 
    MAX(tbl_hardware_assignment.since) AS lastchange, 
    tbl_hardware_assignment.HW_ID,
    tbl_accounts.Login
FROM tbl_hardware_assignment
LEFT OUTER JOIN tbl_accounts ON tbl_hardware_assignment.namenID = tbl_accounts.PersID
AND MAX(tbl_hardware_assignment.since) = tbl_accounts.lastchange
GROUP BY tbl_hardware_assignment.HW_ID,tbl_accounts.Login 

Obțineți ID-urile corecte? Dacă nu, puteți afla care ar trebui să fie relația dintre aceste două tabele?

Comentarii

  • A părut promițător, dar în prezent nu sunt proprietarul a 9 monitoare. A se vedea -> REZULTATE Rezultatul corect trebuie să fie 3 (dispozitive hardware active în cazul în care marca este Samsung) – -.  > Por Daywalker.
  • Mă bazam pe cea de-a doua interogare despre care ați spus că a returnat valorile corecte. Se pare, de asemenea, că aveți intrări duplicate în tbl_typebradmodel, de exemplu, „Monitor 24″ TFT Samsung SyncMaster 2494HM” apare sub typebradmodelIDs [11130, 11840, 11841].  > Por Caleth.
  • Ohh îmi pare rău, aceasta a fost greșeala mea. Am selectat doar un rând suplimentar aici, în acest rezultat (al treilea rând). Acesta este un număr secvențial unic intern pentru fiecare Hardware. (Fapt amuzant: Aceste numere unice sunt înaintea ID-urilor Hardware. Nu știu de ce…). Așadar, nu există intrări duplicate –  > Por Daywalker.
  • Orice alte idei stânga cum aș putea rezolva problema mea? Aș aprecia totul! (Aș putea furniza mai mult câteva mostre de ieșire a tabelelor) – -.  > Por Daywalker.
  • Actualizat cu câteva gânduri suplimentare. –  > Por Caleth.
Welshname

Aș fi pus asta într-un comentariu, dar nu am punctele de rep necesare.

Ați încercat să schimbați îmbinările? Dacă mă uit la rezultat, aș sugera să încerc un INNER JOIN.

Daywalker

Cred că am înțeles elementele de bază

Acest select returnează acum valorile corecte:

    SELECT  tbl_hardware_assignment.ID, 
        tbl_hardware_assignment.HW_ID, 
        tbl_hardware_assignment.namenID, 
        tbl_hardware_assignment.Since,
        tbl_hardware.Knh
FROM tbl_hardware_assignment
INNER JOIN (
            SELECT tbl_hardware_assignment.HW_ID, MAX(tbl_hardware_assignment.ID) lastchange
            FROM tbl_hardware_assignment
            GROUP BY tbl_hardware_assignment.HW_ID
            )
SubSelect ON tbl_hardware_assignment.HW_ID = SubSelect.HW_ID and tbl_hardware_assignment.ID = SubSelect.lastchange
LEFT OUTER JOIN tbl_hardware ON tbl_hardware_assignment.HW_ID = tbl_hardware.HW_ID
WHERE tbl_hardware_assignment.namenID = (SELECT tbl_accounts.PersID FROM tbl_accounts WHERE tbl_accounts.Login = 'MYUSERNAME')
AND tbl_hardware.Aktiv = 1
ORDER BY tbl_hardware.Knh

REZULTAT

ID                  HW_ID         namenID     Since        Knh
------------------- ------------- ----------- ----------- -----------
37481               9778          4993        20130725    11130
32506               10497         4993        20120601    11840
32507               10498         4993        20120601    11841
32511               11554         4993        20120601    12876
34226               13196         4993        20121129    14491
37099               14616         4993        20130701    15896

Cred că aceasta este calea pe care o voi urma, deoarece primele câteva rezultate au fost corecte.

Orice îmbunătățiri sau gânduri cu privire la acest lucru?

Am primit ajutorul final de aici

Vă mulțumesc tuturor pentru ajutor!

Comentarii

  • La naiba… Acum am problema că nu voi obține nici un rezultat Dacă nu a existat nici o atribuire (în timpul creării, sau dacă cineva a omis-o) atunci când caut doar după un model sau altceva decât un utilizator. Aveți vreo idee rapidă despre cum să rezolv această problemă? –  > Por Daywalker.