Kan ikke få LEFT OUTER JOIN til at virke

"Hvis applicationID er 0 så findes der ingen tilsvarende rækker i MeberRoles,"

Det er  i mine ører lidt sort skrevet.
Hvordan kan en række ikke findes hvis du kan tjekke på om den i ApplicationID er = 0..

Ja så må den jo findes:)

Gad vide om du snakker om en helt 3 tabel som har primary key for ApplicationID..

hvis det er tilfældet så er den slet ikke beskrevet og heller ikke med i din SQL.

men jeg har i hvertfald ikke helt forstået spørgsmålet:)

Ok .. har en ApplicationID = 1, 2, N, men ingen 0.

For ApplicationID=1,2..N findes der rækker i MemberRoles.

For at have en og kun en sql sætning som dækker det hele, i det tilfælde hvor  jeg ønsker at liste medlemmer + rolle, men ApplicationID er 0 eller Null, så vil jeg stadig gerne have listen ud.  Jeg havde en ide om at jeg kunne få listet Members, og i de tilfælde hvor der ikke var match på ApplicationID at så få NULLS ud for data i MemberRoles. Men alle mine hidtige krumspring har ikke ledt til det ønskede.

Du kan ikke lave en sql hvor du få en liste ud hvis ApplicationID = et tal eller null.

I så fald vil du altid få dem med null ud for så skal din where inkludere en OR der tjekker på det.

Derfor.. så skal du enten "udbygge" den del på din sql fra f.eks din kode og ellers lave en stored procedure hvor du kan lave T-SQL.

der kan man nemlig også lave if statements osv.


"Hvis jeg da forstod dit spørgsmål:) - ellers så er jeg måske bare for træt i dag:P"

Om min ApplicationID =0 eller Null er ikke pointen.

Mit spørgsmål er : Hvordan får jeg en liste af Members ud, for det tilfælde hvor der for en Member ikke findes en tilsvarende række i MemberRoles? (har leget med IS nULL for applicationID osv.. men fik  så Members ud samme antal gange som der var applications, så det er ikke helt dét:)

Øh - correction - rækekrne findes altid, men gælder kun hvis ApplicationID<>0.

Siger du jeg ikke kan lave noget som dækker alle tilfældene men skal have en anden SQl til at håndtere det tilfælde hvor der ikke findes rækker i MemberRoles svarende til en  eller anden ApplicationID ? ( Og sprocs kommer ikke ind i billedet lige nu, for jeg prototyper stadig)

det siger jeg kun ud fra min forståelse af dit spørgsmål:) måske har jeg jo misforstået;)

prøv følgende til at hente null rækkerne..


SELECT trsMembers.MemberName, trsMemberRoles.Role1 FROM trsMembers LEFT OUTER JOIN trsMemberRoles ON trsMembers.MemberID = trsMemberRoles.MemberID WHERE (ISNUMERIC(trsMemberRoles.ApplicationID)<> 1)

Der findes ingen NULL rækker ! :)


Jeg tror det er mig som har misforstået noget. Rent logisk prøver jeg jo at gifte to tabeller, og samtidig siger til den "hov - ingen rækker til dig i tabel2" :) Måske er det ikke så underligt den ikke henter noget.  Jeg har ikke lyst til at lave tomme rækker kun for at indeholde NULLS (og ApplicationID=0) så det ender nok med at jeg får to forskellige sql'er op at køre.

Jeg tror jeg blev forvirret af, at LEFT OUTER JOIN sagtens kan  hente rækker fra to tabeller, selv om den ene mangler tilsvarende records (i hht join betingelsen). Det nærværende problem er ikke helt det samme, og det er har jeg vist lige opdaget:-)


Medmindre du har et andet forslag så tak for hjælpen og læg et svar.

måske derfor jeg fandt problemstillingen forvirrende:D

men du snupper bare selv pointene;)

men når du har de 2 forskellige SQL'er så kan du i hvertfald prøve at skrive dem herind.. så kan jeg da i hvertfald se hvad de skal trække ud og måske merge dem til en.

Og hvis ikke så kan du holde dem samlet i en stored procedure fremfor oppe i kode laget.

okie - thanks anyways:)

Hehe  - SP må lige vente, for jeg får jo en god idé (eller mange)når jeg har sovet på tingene osv .. u know:)

Tak for tilbudet - det er ikke mere mystisk end at i den ene tilfælde (hvor ApplicationID er 0/Null/Ukendt) så kører jeg så med min JOIN mens i det andet tilfælde så kører jeg blot med Members tabellen for sig selv, og vi er vist enige om at ikke 10 vilde heste kan joine de to tilfælde.

Alt dette var møntet på at kunne nøjes med een datasource til et gridview ( i prototype fasen).

Problemet forsvinder når jeg ved hvad jeg vil, fordi alle mine data ligger i et typed data set, så jeg kan lave hvad jeg har lyst til når jeg når jeg er klar til at switche til tableadapterne.

"Der findes ingen NULL rækker ! :)"

Nej, men i det øjeblik en LEFT JOIN ikke kan finde en tilsvarende række i den tabel der er angivet på højre side, vil den returnere null i alle felter fra denne tabel. Når du så i din WHERE sætning selekterer på et felt fra tabellen på højre side, vil enhver sammenligning returnere false (man kan aldrig så andet end false ved sammenligning med et felt som indeholder null). Derfor er man altid nødt til at tillade NULL, hvis man ønsker at få returneret alle rækker fra tabellen på venstre side. I dit tilfælde er du nødt til at ændre din WHERE til

WHERE (trsMemberRoles.ApplicationID IS NULL) OR (trsMemberRoles.ApplicationID = @ApplicationID)

Det du foreslår er afprøvet 13/10-2007 21:06:00 og resultatet er som beskrevet der :-(

men hvis du har et andet forslag så lytter jeg gerne - måske kan jeg lære noget af det:)

Øh - nu har jeg læst din tekst en gang til. Og jeg har ingen rækker i MemberRoles hvor ApplicationID er 0 eller null, og dette er med vilje, da jeg ikke ønsker at fylde dummy data på, fordi jeg ikke kan kode ting ordentligt:-)

Det er jo netop mit pointe. Men jeg formulerede mig nok forkert. Så lad mig prøve en gang til. :-)

Når du f.eks. udfører en

SELECT m.MemberName, r.Role1
FROM trsMembers m
LEFT OUTER JOIN trsMemberRoles r ON m.MemberID = r.MemberID
WHERE (r.ApplicationID = @ApplicationID)

selekterer du på et felt, som potentielt kan være NULL (hvis der ikke findes rækker, hvor tabellerne kan sammenkædes på baggrund af MemberID). Det giver ikke rigtigt mening.

Hvis du ønsker alle sammensatte rækker (også dem hvor ApplicationID er 0), skulle ApplicationID feltet placeres i trsMembers tabellen i stedet. Så ville det være muligt uden problemer at udføre den krævede operation.

Hmm... Hvordan kan ApplicationID nogen sinde være 0, med mindre feltet også findes i trsMembers? Gør det det (du har ikke vist den komplette tabelstruktur)? Så er det jo bare at ændre det til at selektere på trsMembers.ApplicationsID i stedet.

Den kan ikke være lig med nul som sådan- det var blot et forsøg på at indikere at der ikke findes nogen applikation:

Jeg har en liste af Members. Så har jeg forskellige applikationer, og for hver applikation er der et sæt privilegier (roller - Role1, Role2..) som jeg har smidt i en record i MemberRoles. Deraf den kompositte PK for MemberRoles.

For applikationer som IKKE eksisterer er der ingen roles naturligvis. Nu prøvede jeg så på at få "unisex" kode som kunne vise mig Members og deres roller for hver applikation, SAMT også blot listen af members (hvor ApplicationID er udefineret, fordi der ikke er nogen roller til noget som ikke er en applikation).  Og jeg er nået til den erkendelse at det kan jeg bare ikke :)

Eksempel  output (applikation 1)

MemberName      Role1        Role2
name1            yes        yes
name2            no          yes


Eksempel for ønsket output, når vi ikke taler om nogen specifik applikation (dvs ApplicationID = 0/null/whatever)
MemberName    Role1        Role2
Name1          null          null
Name2          null          null

Inden du bruger for megen energi på at hjælpe mig, så er problemet "løst" da jeg nu midlertidigt bruger 2 sql sætninger, og snart smider det hele ud alligevel, da jeg har mit dataset, hvor jeg nemt kan finde alle de ting jeg ønsker. Det var blot nemmest for mig at bikse med brugerfladen vha sqldatasources, som så forsvinder når jeg er tilfreds:-)

Okeydokey - god fornøjelse... :-)

Hej Neoman.
Jeg ved ikke om det er noget i denne stil du kan bruge:
SELECT trsMembers.MemberName, trsMemberRoles.Role1 FROM trsMembers
LEFT JOIN trsMemberRoles ON trsMembers.MemberID = trsMemberRoles.MemberID WHERE (trsMemberRoles.ApplicationID = CASE WHEN @ApplicationID=0 THEN NULL ELSE @ApplicationID END)

Tak dr_chaos - men det funker heller ikke - beklager jeg har optaget din, og andres  tid med det.

Nu skal jeg vist slå en stor grim pløk igennem hjertet på min idiotiske SQL :=)

Jeg undrede mig lidt hvorfor det ikke kunne køre, og granskede så lige docs.

Lidt back-to-basics (for mig selv og evt. andre fumlere på mit niveau):

1. Når man har en SQL med et joins og WHERE, så bliver resultatsettet i første omgang dannet udfra det som står på JOIN betingelsen
2. Derpå filtereres resultatet med WHERE betingelsen


JOIN-typen, dvs ting som INNER/OUTER osv har kun indvirkning på det første resultatset, hvor man så passende kan vælge om man vil have det hele fra den venstre tabel, den højre tabel (og i begge fald nulls fra den anden tabel ved manglende match), fra begge tabeller,  eller kun de rækker hvor der er match på begge sider -altså match på JOIN BETINGELSEN.

Først efterfølgende,  så filtreres det oprindelige resultatset med det som måtte være anført i WHERE.


Min tanketorsk gik på at "jamen hvis det ikke findes i den højre tabel, så laver jeg da blot en OUTER JOIN, og så får jeg da i det mindste listet min venstre tabel og med nulls fra den højre". Konceptuelt ville det kræve, at db'en evaluerede WHERE-betingelsen INDEN  join-betingelsen, og det er jo noget totalt nonsense:)

R.I.P. :-)

Hvad output giver min ?

Der kommer intet ud for ApplicationID=0.

Hvad får du ved
SELECT trsMembers.MemberName, trsMemberRoles.Role1 FROM trsMembers
LEFT JOIN trsMemberRoles ON trsMembers.MemberID = trsMemberRoles.MemberID WHERE (trsMemberRoles.ApplicationID IS NULL )

Det samme som før: ingenting. (Husk at jeg ikke har nogen rækker med ApplicationID=0 eller IS NULL ApplicationID - det var min ikke gennemtænkte anvendelse af outer join som skulle trylle ting frem, men det kan man bare ikke:)

altså hvis de kun skal findes i venstre tabel burde
SELECT trsMembers.MemberName, trsMemberRoles.Role1 FROM trsMembers
LEFT JOIN trsMemberRoles ON trsMembers.MemberID = trsMemberRoles.MemberID WHERE (trsMemberRoles.MemberID IS NULL )

Være vejen frem. Det er det samme som en not in men den er bare hurtigere.

Men den finder skam også en hel masse som opfylder JOIN betingelsen,  hvis og kun hvis WHERE betingelsen fjernes. Og derpå kommer så WHERE ind, og fjerner ALLE rækkerne fordi ingen rækker opfylder WHERE betingelsen. Nu er du vel ikke faldet ind i samme fælde som mig ? :)

Eksempel på data jeg kører med :
Members
---------
MemberID MemberName
195    k7jlk7
196    j7låæø
197    k7jlk
198    abc

MemberRoles
-----------
MemberID ApplicationID Role1 Role2
195    1    True    False
195    2    True    False
195    3    True    False
196    1    True    False
196    2    True    False
196    3    True    False

Nå ja - 197, 198 har tilsvarende data, fik bare ikke copy/pastet dem

Og min forkerte pointe var at have EEN sql som dækkede alle tilfælde, og det kan man bare ikke:)

I øvrigt - tak også for din indsats dr_chaos. Jeg har for længst lavet en by-pass operation på "problemet", så det er pist væk:).

oki

Jeg skal nok holde mund fra nu af, men efter lige at have taget en lur, vågnede jeg op med et sæt og har haft åndenød lige siden, så jeg er nødt til at komme af med det! Jeg tror jeg ved, hvor vi gik forkert af hinanden:

SELECT trsMembers.MemberName, trsMemberRoles.Role1
FROM trsMembers
LEFT OUTER JOIN trsMemberRoles
  ON trsMembers.MemberID = trsMemberRoles.MemberID
WHERE (COALESCE(trsMemberRoles.ApplicationID, 0) = @ApplicationID)

Det må være svaret! COALESCE to the rescue! Så kan du selektere alle de utilknyttede ved at angive en applikationID på 0.

Har jeg ret?

Jeg har ikke orket at teste, fordi jeg lige har fundet løsningen ! :)

Jeg gennemgik, i hovedet, den tekst jeg skrev 14/10-2007 12:12:33 og løsningen dukkede op. Det er jo selve join-betingelsen jeg skulle modificere, for at få rækker ud af den venstre tabel selv om der ikke var matches i den højre. Nu har jeg prøvet det og det virker ! :)

SELECT m.MemberName, r.Role1 FROM trsMembers m LEFT JOIN trsMemberRoles  r ON m.MemberID = r.MemberID AND (r.ApplicationID = @ApplicationID)

Whoopieee :)
PS: Jeg ved ikke helt hvad COALESCE gør - men nu bliver jeg jo nødt til at finde ud af det, hehe:)

Hmm mu har jeg læst på den, og faktisk også testet just in case, men det giver stadig intet når ApplicationID =0 eller null. Den kommer jo heller ikke udover det fundamentale, at det er join-betingelsen som bestemmer hvad man kan filtere i, og de rækker findes simpelthen ikke til at blive filtreret af WHERE.

Eller, fandtes ikke, indtil den brain-wave dumpede ned fra himlen:)

Ikke for at nedgøre din viden, men det synes nu ikke så fundamentalt, for selvfølgelig findes rækkerne når du bruger en LEFT JOIN. Prøv at køre den oprindelige select uden where-betingelse. Jeg er sikker på, at de manglende rækker findes i sættet (må bare håbe, at du ikke har en tabel på flere millioner rækker, så kan det selvfølgelig godt blive lidt anstrengende at lede efter de rigtige).
De burde nu komme frem, hvis man filtrerer med
WHERE (trsMemberRoles.ApplicationID IS NULL)

(gør de ikke det, så taler/talte vi VIRKELIG forbi hinanden!)

Hvis de gør det, betyder det til gengæld, at COALESCE burde virke, for det den gør (jeg forstår, at du ikke kender den?) er jo, at de konverterer (NULL = ingen værdi) til en værdi (i mit tilfælde 0).

Nå, jeg kan forstå at du faktisk ikke gider lege - og min åndenød har også fortaget sig :-) -- så måske skulle vi bare lade den brainwave stå og blafre uforløst i luften...

Jeg ved ikke om vi taler forbi hinanden eller ej og det gør heller ingen forskel.

Jeg er lidt overrasket over, at ingen af bidragsyderne her, som jeg formoder har væsentligt mere ekspertise på sql-fronten end jeg, kunne gennemskue, at jeg havde lavet en fundamental fejl, og heller ikke var i stand til lige at køre en lille test selv.

Hvis du nu prøver at kigge på 14/10-2007 14:57:13, så vil du, måske med lidt gransken, måske også nå frem til, at den join-betingelse jeg oprindeligt havde,
altså  ON trsMembers.MemberID = trsMemberRoles.MemberID, ikke kunne fremtrylle nogen andre rækker, uanset om det er en INNER JOIN, LEFT JOIN eller whatever JOIN (hvis ikke - så prøv det selv). Og man kan filtrere alt det man har lyst til, men det kræver, at der er noget at filtere på, og DET ER DER IKKE, så længe jeg holdt fast på min oprindelige join-betingelse.

Jeg prøver ikke at på at overbevise nogen om det modsatte,eller noget som helst. Prøv blot at lave to tabeller selv og joine dem som beskrevet. Jeg selv fik rodet med det i nogle timer, og prøvet alskens permutationer, og nu ved jeg hvad der virker eller ej, og hvor jeg var gået galt i byen.

BTW: Normalt så føler jeg mig forpliget til at svare på indlæg, men dette problem er nu løst, og hermed føler jeg mig frigjort fra den normale sociale kontrakt man får ved at skrive spørgsmål herinde. Med andre ord - emnet er nu lukket for good:)

Jeg har nu taget dig på ordet (burde jeg selvfølgelig have gjort allerede) og oprettet de to tabeller med data som beskrevet i dit indlæg 14/10-2007 14:57:13.

Herefter har jeg oprettet en forespørgsel som i mit indlæg 14/10-2007 20:19:31

Når jeg kører denne forespørgsel får jeg:

Med @ApplicationID = 3:

MemberName    Role1    Role2
k7jlk7    True    False
j7låæø    True    False


Med @ApplicationID = 2:

MemberName    Role1    Role2
k7jlk7    True    False
j7låæø    True    False

Med @ApplicationID = 1:

MemberName    Role1    Role2
k7jlk7    True    False
j7låæø    True    False

Med @ApplicationID = 0:
MemberName    Role1    Role2
k7jlk    Null    Null
abc    Null    Null


Det er efter min bedste overbevisning det rigtige output og også det du efterlyser for 0 situationen i din kommentar 14/10-2007 01:20:09

Du er naturligvis ikke forpligtet til at svare, men jeg følte bare trang til at sætte det på plads... :-)