[SQL] Gegevens uit dezelfde tabel ophalen

[shades684]

Ik zit al een tijdje over een probleem te blokken. Ik heb een oplossing, maar ik heb het gevoel dat ik veel te moeilijk aan het denken ben.

Goed hier wat uitleg dan,
we hebben een tabel met 3 belangrijke kolommen (allen numbers), namelijk
ACCOUNT, ID, ROL

per account kunnen er in deze tabel meerdere entries staan. Aan een account zitten nl meerdere personen vast en elke persoon heeft een bepaalde rol binnen dat account (stuk of 30 mogelijkheden)

Wat wil ik doen.
Ik krijg een account binnen, dit account heeft een hoofdgebruiker (rol=1) en misschien een medegebruiker(rol = 2). Een hoofd of medegebruiker die inlogt op dit account a mag ook gebruik maken van een account b in de volgende gevallen

• Er is alleen een hoofdgebruiker : Deze persoon mag ook van het andere account gebruik maken als hij hoofdgebruiker is van account b en er is in account b geen medegebruiker
• Er is een hoofdgebruiker en een medegebruiker: Deze persoon mag van het andere account gebruik maken als hoofdgebruiker a = hoofdgebruiker b en medegebruiker a = medegebruiker b, of als hoofdgebruiker a = medegebruiker b en medegebruiker a = hoofdgebruiker b.

Vraag is uiteraard, gegeven het account waarop de gebruiker inlogt (waar hij uiteraard nog ook een van de andere rollen kan hebben), op welke andere accounts mag de gebruiker inloggen

Wat ik zelf had bedacht komt ongeveer op het volgende neer (beetje pseudo code, dus let niet teveel op haakjes etc)

code:

select distinct
    ACCOUNT,
from
    ACCOUNT_TABLE
where
    (ROLE = 1 or ROLE = 2)
and
    ID in 
    
    (
    select
        ID,     
    from
        ACCOUNT_TABLE   where
        (ROLE = 1 or ROLE = 2)
    and 
        ACCOUNT= :account //(invoer account)
    
    )
and
    (select
        count(ACCOUNT),
    from
        ACCOUNT_TABLE
    where
        (ROLE = 1 or ROLE = 2)
    and
        ID in 
        
        (
        select
            ID,     
        from
            ACCOUNT_TABLE
        where
            (ROLE = 1 or ROLE = 2)
        and 
            ACCOUNT_REF= :account_ref //(invoer account)

        
        )
    group by account) = 
    (
        select
            count(ID),  
        from
            ACCOUNT_TABLE
        where
            (ROLE = 1 or ROLE = 2)
        and 
            ACCOUNT_REF= :account_ref //(invoer account)
        
    )

Dit moet toch makkelijker kunnen, maar ik zit even goed vast.

P.S. Ik kan de tabel etc niet aanpassen, daar zit ik gewoon aan vast

[lier]

Zou je een klein stukje (fictieve) vulling van de tabel in combinatie met een voorbeeld kunnen geven ?
Ik begrijp het namelijk niet (maar kan ook aan mij liggen).

[shades684]

Tja is ook lastig uit te leggen en de ristricties zijn ook raar (heb ze zelf niet bedacht) But I'll give it a go


ACCOUNT, ID, ROL
1, 10, 1
1, 11, 2
1, 12, 3
1, 13, 4
1, 14, 5
1, 15, 6
2, 10, 2
2, 11, 1
3, 10, 1
3, 13, 2
4, 10, 1
5, 10, 1
5, 11, 2
5, 15, 3

stel we komen op account 1 binnen waar id 10 hoofdgebruiker en 11 medegebruiker is. Dan zouden we ook op account 2 in mogen loggen, want hoofd+medegebruiker combo zijn hetzelfde. Op acount 3 zou dit niet mogen omdat alleen de hoofdgebruikers gelijk zijn, op 4 geld hetzelfde. Op 5 zouden we wel weer in mogen loggen

[lier]

Het verhaal is nu duidelijk, het gekozen datamodel totaal niet.
Maakt niet uit...

Mag het een TSQL oplossing zijn (oftewel MSSQL) ?

[shades684]

Ik weet het,
wij gebruiken hier Oracle SQL (PLSQL).

[lier]

Even uit de vrije pols:

code:

CREATE  TABLE #table1 (ACCOUNT int, ID int, ROL int)
CREATE  TABLE #table2 (ACCOUNT int, ID int, ROL int)
CREATE  ID1, ID2 INT

SET ID1 = (SELECT ID FROM ACCOUNT_TABLE WHERE ACCOUNT = account AND ROLE = 1)
SET ID2 = (SELECT ID FROM ACCOUNT_TABLE WHERE ACCOUNT = account AND ROLE = 2)

INSERT  INTO #table1
SELECT  ACCOUNT, ID, ROL FROM ACCOUNT_TABLE WHERE ID = ID1

INSERT  INTO #table2
SELECT  ACCOUNT, ID, ROL FROM ACCOUNT_TABLE WHERE ID = ID2

SELECT  ACCOUNT
FROM    #table1 tbl1
INNER   JOIN #table2 tbl2
ON  tbl1.ACCOUNT = tbl2.ACCOUNT

Misschien nog even de juiste syntax !?

Uitleg:
Stap 1: Je bepaald de verschillende IDs (hoofd en medegebruiker)
Stap 2: Selecteer de ACCOUNTS waarbij de hoofdgebruiker gelijk is / voor komt
Stap 3: Selecteer de ACCOUNTS waarbij de medegebruiker gelijk is / voor komt
Stap 4: Voer een inner join op deze verzameling uit (alle ACCOUNTS, inclusief je parameter account)

[ Voor 22% gewijzigd door lier op 19-07-2006 14:59 ]

[Brupje]

op basis van lier heb ik volgende verzonnen:

code:

select id1 = (select id from account_table where account =1 and role=1)
select id2 = (select id from account_table where account =1 and role=2)


select act1.account account
   from 
    account_table act1
    , account_table act2
   where act1.account=act2.account
    and (((act1.id=id1 and (act1.rol=1 or act1.rol=2)) and (act1.id=id2 and (act2.rol=1 or act2.rol=2) and act1.rol<>act2.rol)))
    or ((act1.id=id2 and (act1.rol=1 or act1.rol=2)) and (act1.id=id1 and (act2.rol=1 or act2.rol=2) and act1.rol<>act2.rol)))

geen idee of het doet wat je bedoeld, maar misschien helpt het je in de goeie richting

overigens zou ik me ernstig afvragen of je niet met een zware design fout te maken hebt en je niet beter die kunt oplossen.

edit: ff aanpassing, klopt beter denk zo...

[ Voor 38% gewijzigd door Brupje op 19-07-2006 15:16 ]

[DND_Roche]

Het is inderdaad niet zo'n nette oplossing, maar goed daar kunnen we soms niets aan doen.

Vraagje: is het mogelijk dat er binnen 1 account meerdere hoofd-/medegebruikers zijn? of heb je er van beide ten hoogste 1?

[shades684]

Bedankt voor de input,

zoals ik hem ff lees,
in tabel 1 worden alle hoofdgebruikers gezet
in tabel 2 worden alle medegebruikers gezet

vervolgens selecteer je de accounts die in beide tabellen voorkomen.

Wat nu, als er alleen een hoofdgebruiker is en geen medegebruiker op het account dat ingevoerd wordt (Dit kan ook), probleem is dat ALS er een medegebruiker is deze ook vertegenwoordigt moet zijn in account b.

[ Voor 18% gewijzigd door shades684 op 19-07-2006 15:27 ]

[shades684]

Er is hoogstens maar 1 hoofd en een medegebruiker. Er is uiteraard altijd een hoofdgebruiker.
Uiteraard hou ik mijn opties graag open wat dat betreft. Je weet nooit met wat voor rare ideeen men af toe op de proppen komt.

[Brupje]

kun je je voorbeeld eens uitbreiden met die situatie? dat wordt het misschien wat duidelijker voor mij

[lier]

Ik maak gebruik van tijdelijke tabellen, de syntax van MSSQL is #[tabelnaam].
Moeten uiteraard nog wel even expliciet aan het eind gedropt worden, maar is niet iets waar de DBA verdrietig van wordt.

Uitbreiding voor de tweede situatie:

code:

CREATE  TABLE #table1 (ACCOUNT int, ID int, ROL int)
CREATE  TABLE #table2 (ACCOUNT int, ID int, ROL int)
DECLARE @ID1 INT, @ID2 INT

SET @ID1 = (SELECT ID FROM ACCOUNT_TABLE WHERE ACCOUNT = account AND ROLE = 1)
SET @ID2 = (SELECT ID FROM ACCOUNT_TABLE WHERE ACCOUNT = account AND ROLE = 2)

IF  ID2 IS NULL
BEGIN

SELECT  ACCOUNT FROM ACCOUNT_TABLE WHERE ID = @ID1
AND ACCOUNT NOT IN (SELECT ACCOUNT FROM ACCOUNT_TABLE WHERE ID != @ID1)

END

ELSE

BEGIN

INSERT  INTO #table1
SELECT  ACCOUNT, ID, ROL FROM ACCOUNT_TABLE WHERE ID = @ID1

INSERT  INTO #table2
SELECT  ACCOUNT, ID, ROL FROM ACCOUNT_TABLE WHERE ID = @ID2

SELECT  ACCOUNT
FROM    #table1 tbl1
INNER   JOIN #table2 tbl2
ON  tbl1.ACCOUNT = tbl2.ACCOUNT

END

DROP    TABLE #table1
DROP    TABLE #table2

Toegevoegd is nu de controle of de ACCOUNT ook medegebruiker is. Indien niet selecteer de hoofdgebruikers met dezelfde ID die niet medegebruiker zijn.

[ Voor 8% gewijzigd door lier op 19-07-2006 15:35 ]

[DND_Roche]

Wat een rot-constructie

Uit mijn hoofd even dit geklopt, zonder testgegevens in een database (ik ben even te beroerd om zelf een tabelletje te maken, want ik ga zo naar huis ) kom ik op zoiets uit:

SQL:

Select Distinct a.account
  From ( Select h.account
              , h.id hoofd_id
              , m.id mede_id
           From accounts h
              , accounts m
          Where m.account(+) = h.account
            And h.rol = 1
            And m.rol(+) = 2) a
     , ( Select Sum( id) id
           From accounts
          Where rol = 1
            And account = :gegeven_account) hoofdrol
     , ( Select Sum( id) id
           From accounts
          Where rol = 2
            And account = :gegeven_account) mederol
Where ( a.hoofd_id = hoofdrol.id Or
        Nvl( a.mede_id, hoofdrol.id) = hoofdrol.id)
  And ( mederol.id = 0 Or
        ( a.hoofd_id = mederol.id Or
          Nvl( a.mede_id, mederol.id) = mederol.id));

Dit is aangenomen dat er 1 hoofd- en 0 of 1 medegebruiker is;
Subquery a is alle accounts met de hoofd en eventuele medegebruiker in 1 record;
Subquery hoofdrol is het id van de hoofdgebruiker van het gegeven account.
Subquery mederol is het id van de medegebruiker van het gegeven account.

Ik sommeer zodat ik in ieder geval een resultaat heb (0) dat ik kan matchen tegen a.

[ Voor 2% gewijzigd door DND_Roche op 19-07-2006 15:39 . Reden: typo ]

[Brupje]

lier schreef op woensdag 19 juli 2006 @ 15:34:
Ik maak gebruik van tijdelijke tabellen, de syntax van MSSQL is #[tabelnaam].
Moeten uiteraard nog wel even expliciet aan het eind gedropt worden, maar is niet iets waar de DBA verdrietig van wordt.

in oracle doen we dat eigenlijk niet. alleen al omdat je dan een normale gebruiker create table rechten moet geven in een schema. daarbij moet dit ook wel zonder kunnen denk ik

[Brupje]

SQL:

select id into id1 from account_table where account =1 and role=1;
begin
    select id into id2 from account_table where account =1 and role=2;
exception
    when no_data_found then
        id2 := id1; -- er is maar 1 rol, hoofdgebruiker nl
end;

select act1.account account
   from 
    account_table act1
    , account_table act2
   where act1.account=act2.account
    and (act1.id=id1 and act2.id=id2)
    or (act1.id=id2 and act2.id=id1)

code weer beetje aangepast met situatie dat er maar 1 rol is

[lier]

Mijn oplossing is alleen bedoeld om de complexiteit weg te nemen.
Ik weet zeker dat er 1001 andere oplossingen zijn die het "ook" doen.
Misschien kan het gebruikt worden als springplank naar de "ultieme" oplossing ?

Begin een beetje oververhit te raken, sry...

[shades684]

Ik was al aan het zoeken of we zoiets in Oracle voor elkaar konden krijgen, want ik had daar nog niet op gezocht. Toch grappig hoe andere mensen zulke queries uit hun mouw schudden, ik had op de mijne toch best wel lang zitten denken. Hardstikke bedankt.

[Brupje]

lier schreef op woensdag 19 juli 2006 @ 15:43:
Mijn oplossing is alleen bedoeld om de complexiteit weg te nemen.
Ik weet zeker dat er 1001 andere oplossingen zijn die het "ook" doen.
Misschien kan het gebruikt worden als springplank naar de "ultieme" oplossing ?

Begin een beetje oververhit te raken, sry...

was niet negatief bedoeld hoor, dankzij jou code snapte ik in ieder geval het probleem ;p

[shades684]

Misschien moet ik maar eens een boek Explaining problems for Dummies kopen.

[lier]

offtopic:
Noem je ons nou echt Dummies !?
(of had er dan Explaining problems to dummies gestaan ?

[shades684]

Deze doet uiteinelijk wat ik bedoelde (en is volgens mij leesbaarder dan de mijn), ik begreep bij het uitzoeken van de hoofd en mede-rol alleen niet waarom er Sum werd gebruikt en de laatste where/or in de query bedoelde ik zo.

SQL:

        SELECT a.ACCOUNT
        FROM ( SELECT h.ACCOUNT
                , h.ID hoofd_id
                , m.ID mede_id
                 FROM ACCOUNT h
                , ACCOUNT m
                WHERE m.ACCOUNT(+) = h.ACCOUNT
                AND h.role = 1
                AND m.role(+) = 2) a
            ,   ( Select ID
                FROM ACCOUNT
                WHERE ROLE = 1
                AND ACCOUNT = :account) hoofdrol
            , ( SELECT ID
                FROM ACCOUNT
                WHERE ROLE = 2
                And ACCOUNT = :account) mederol
        WHERE ( a.hoofd_id = hoofdrol.ID ) AND (a.mede_id=mederol.ID)
        OR    ( a.hoofd_id = mederol.ID ) AND  (a.mede_id=hoofdrol.ID)

[ Voor 5% gewijzigd door shades684 op 20-07-2006 09:54 ]

[DND_Roche]

Dat is omdat ik het uit mijn hoofd moest doen
Omdat ik niet helemaal zeker was hoe de database ging reageren als er GEEN mederol gevonden werd.

SQL:

<knip>
            , ( SELECT ID
                FROM EB.ACCOUNT_DOMAIN
                WHERE ROLE = 2
                And ACCOUNT = :account) mederol
        WHERE ( a.hoofd_id = hoofdrol.ID ) AND (a.mede_id=mederol.ID)
        OR    ( a.hoofd_id = mederol.ID ) AND  (a.mede_id=hoofdrol.ID
<knip>

Als er nu geen resultaat komt uit de subselect mederol (omdat het mogelijk is dat er alleen een hoofdrol bij een account bestaat en geen mederol) wat gaat de where-statement nu doen?

mederol.ID is nu NULL; dus er staat (a.mede_id=NULL) of (a.hoofd_id = NULL)
Met Oracle kan ik (en wil ik) daar niet op vertrouwen dat dat goed gaat.

Door nu een SUM(id) te doen, krijg WEL resultaat uit de subquery mederol zijnde 0!
Door nu 0 altijd goed te keuren zijn ook accounts met alleen een hoofdrol goed.

Ik kon zonder testset niet kijken of het wel goed ging, daarom het zekere voor het onzekere genomen.

Maar goed, je bent er uit.
Even een laatste opmerking; dit gaat fout zodra je meer dan 1hoofd- of medegebruiker per account definieerd. (Omdat subquery a dit niet toestaat)
Wil je dit dynamischer gaan opzetten ben ik toch bang dat je moet kijken naar een betere structuur van deze tabellen

[Verwijderd]

shades684 schreef op woensdag 19 juli 2006 @ 17:26:

SQL:

        WHERE ( a.hoofd_id = hoofdrol.ID ) AND (a.mede_id=mederol.ID)
        OR    ( a.hoofd_id = mederol.ID ) AND  (a.mede_id=hoofdrol.ID)

Weet niet precies hoe Oracle hiermee omgaat, maar ik ben altijd heel voorzichtig met OR en haakjes, dus ik zou er in dit geval altijd een paar toevoegen...
Of 't nou PL/SQL, TSQL, ISQL, whatever is.

SQL:

        WHERE ( ( a.hoofd_id = hoofdrol.ID ) AND (a.mede_id=mederol.ID) )
        OR    ( ( a.hoofd_id = mederol.ID ) AND  (a.mede_id=hoofdrol.ID) )

[shades684]

true that,
ik heb even gekeken en als in oracle beide kanten van de vergelijking een lege set zijn, dan komt er evalueert dit ook naar true.

Maar idd, als er meerdere hoofd of nevenrollen zijn, dan is er een probleem.

[ Voor 93% gewijzigd door shades684 op 20-07-2006 09:37 ]