SQL uit 2 tabellen

[Guillome]

Hoi,

Ik ben opzich wel thuis in de SQL wereld, maar dit lukt me even niet.

Ik gebruik MSSQL

Ik heb 2 tabellen: competenties, en competentie_werkproces.

In de competentielijst staan tig onafhankelijke/losstaande competenties.
competentie_ID, naam

In de competentie_werkproces tabel staan de koppelingen voor de koppeling werkproces (een 3e, voor nu onbelanrijke, tabel) -> competentie.
competentie_ID, werkproces_ID

Elke competentie kan dus bij meerdere werkprocessen horen, maar mag maximaal 1 keer voorkomen per werkproces.
Ik wil nu een lijst krijgen van alle competenties die nog ingevuld kunnen worden bij een werkproces.

Oftewel: Hij moet kijken welke competenties er bij werkproces_ID XX horen, en alles BEHALVE die resultaten terug geven.

Competenties
competentie_ID, naam
1, Test
2, Tost
3, Blaat
4, Nieuw

competentie_werkproces
competentie_ID, werkproces_ID
1, 1
2, 1
3, 1
1, 2
3, 2

Als ik nu 'in werkproces_ID 2 zit', dan wil ik alleen competentie met ID 2 en 4 terug krijgen.

Hielfe?

[BasieP]

zoek eens op 'join' of 'joins' / subqueries

ow en wat had je al?


(mm tijd over, werkt dit?)

SQL:

SELECT * FROM `competenties` AS `c` INNER JOIN `competentie_werkproces` AS `cw` ON `cw`.`competentie_ID` <> `c`.`competentie_ID` WHERE `cw`.`werkproces_ID` = '<je nummer hier>' GROUP BY `c`.`competentie_ID`

[ Voor 106% gewijzigd door BasieP op 27-06-2007 12:23 ]

[Guillome]

Inner, left of rightjoints werken niet. Ik had dit, maar dan pakt ie dubbele records:

code:

SELECT c.*, wp.werkproces_ID as wpID FROM cgl_competenties as c LEFT JOIN cgl_competentie_werkproces as wp ON wp.competentie_ID = c.competentie_ID ORDER BY nummer

[BtM909]

Ik mis een klein beetje je query wat je zelf hebt geprobeerd

Heel snel gescand, lijkt het erop dat je IN en een subquery moet gebruiken. Het lijkt erop dat je vraagstelling een beetje vaag is (met name je laatste zin )

[Gonadan]

SQL:

select distinct naam from competenties cp, competenties_werkproces wp where sp.competentie_ID != wp.competentie_ID

Je bent écht thuis in de SQL wereld?

[ Voor 6% gewijzigd door Gonadan op 27-06-2007 12:21 ]

[Guillome]

@godananan, die is niet helemaal goed-> ik mis nu nog de kolom werkproces_ID zodat ik kan filteren. Of het moet in de SQL al kunnen. Ik moet namelijk alle competenties die nog niet bestaan in dit werkproces. Nu pakt ie gewoon alle competenties.
En ja ik ben er wel in thuis.
@BTM, ik ga kijken naar subqueries en IN
Mn vraag is ook wellicht wat vaag, het is ook lastig uit te leggen.

[ Voor 36% gewijzigd door Guillome op 27-06-2007 12:23 ]

[BasieP]

mm zie mijn update van me post..

[Gonadan]

Guillome schreef op woensdag 27 juni 2007 @ 12:22:
@godananan, die is niet helemaal goed-> ik mis nu nog de kolom werkproces_ID zodat ik kan filteren. Of het moet in de SQL al kunnen. Ik moet namelijk alle competenties die nog niet bestaan in dit werkproces. Nu pakt ie gewoon alle competenties.
En ja ik ben er wel in thuis.

Een clausule vergeten.

SQL:

select distinct naam 
from competenties cp, competenties_werkproces wp 
where sp.competentie_ID != wp.competentie_ID
and wp.werkproces_ID=2

Maar dat had je toch ook zelf kunnen doen?

[Guillome]

@BasieP:
" Column 'c.nummer' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

[Guillome]

Gonadan schreef op woensdag 27 juni 2007 @ 12:27:
[...]

Een clausule vergeten.

SQL:

select distinct naam 
from competenties cp, competenties_werkproces wp 
where sp.competentie_ID != wp.competentie_ID
and wp.werkproces_ID=2

Maar dat had je toch ook zelf kunnen doen?

@godananan, die is niet helemaal goed-> ik mis nu nog de kolom werkproces_ID zodat ik kan filteren. Of het moet in de SQL al kunnen. Ik moet namelijk alle competenties die nog niet bestaan in dit werkproces. Nu pakt ie gewoon alle competenties.

SQL:

select distinct naam 
from cgl_competenties as cp, cgl_competentie_werkproces as wp 
where cp.competentie_ID != wp.competentie_ID
and wp.werkproces_ID=4

[ Voor 12% gewijzigd door Guillome op 27-06-2007 12:28 ]

[Gonadan]

Guillome schreef op woensdag 27 juni 2007 @ 12:28:

SQL:

select distinct naam 
from cgl_competenties as cp, cgl_competentie_werkproces as wp 
where cp.competentie_ID != wp.competentie_ID
and wp.werkproces_ID=4

Wat probeer je te zeggen?
Wil je dit:

SQL:

select distinct naam, competentie_ID
from cgl_competenties as cp, cgl_competentie_werkproces as wp 
where cp.competentie_ID != wp.competentie_ID
and wp.werkproces_ID=4

Edit: Nee ook niet.

[Guillome]

Hij moet in principe ALLE competenties laten zien.
Echter. Hij moet even in de tabel cgl_competentie_werkproces kijken welke competenties al gekoppeld zijn aan het werkproces welke je nu geopend hebt (in mijn voorbeeld hier net boven 4), en al die competenties achter wege laten.

[Gonadan]

Guillome schreef op woensdag 27 juni 2007 @ 12:30:
Hij moet in principe ALLE competenties laten zien.
Echter. Hij moet even in de tabel cgl_competentie_werkproces kijken welke competenties al gekoppeld zijn aan het werkproces welke je nu geopend hebt (in mijn voorbeeld hier net boven 4), en al die competenties achter wege laten.

Dat doet hij toch? Of ben ik nou gek.

[Guillome]

Ik weet niet of je gek bent, maar met jouw query doet ie dat niet

Jouw output: (met deze query)

SQL:

select distinct naam, werkproces_ID, cp.competentie_ID
from cgl_competenties as cp, cgl_competentie_werkproces as wp 
where cp.competentie_ID != wp.competentie_ID
and wp.werkproces_ID=4

code:

Naam, werkproces_ID, competentie_ID
Analyseren  4   1
Beslissen en activiteiten initieren   4   2
Ethisch en integer handelen     4    3
Formuleren en rapporteren     4  4
Overtuigen en beinvloeden    4   5
Plannen en organiseren     4   6
Samenwerken en overleggen    4    7

Inhoud van competentie_werkproces tabel

code:

Comp_ID, werkproces_ID
4, 4
5, 4
6, 4
7, 4

Inhoud van competentielijst: 7 competenties

Wat ik wil is:

code:

Beslissen en activiteiten initieren   4   1
Ethisch en integer handelen     4    2
Samenwerken en overleggen    4    3

[ Voor 108% gewijzigd door Guillome op 27-06-2007 12:38 ]

[Gonadan]

Ik heb je tabel uit de TS overgenomen en getest, ik heb inderdaad een denkfout gemaakt, sorry.

SQL:

select naam 
from competenties 
where competentie_id 
  not in (select competentie_id 
             from competenties_werkgroep where werkgroep_id=2)

Zoiets?

[Guillome]

DAT lijkt m te zijn Even uitproberen!

[BtM909]

Dus nogmaals subquery en IN.... Toch iets beter verdiepen in de materie

[BasieP]

Guillome schreef op woensdag 27 juni 2007 @ 12:27:
@BasieP:
" Column 'c.nummer' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

die error kan je wel wegwerken natuurlijk, gewoon je * vervangen door de kolommen die je wilt.

ben wel benieuwd of mijn methode ook werkt, aangezien het een stuk sneller is dan een subquery

[Gonadan]

BasieP schreef op woensdag 27 juni 2007 @ 12:43:
die error kan je wel wegwerken natuurlijk, gewoon je * vervangen door de kolommen die je wilt.

ben wel benieuwd of mijn methode ook werkt, aangezien het een stuk sneller is dan een subquery

Jouw methode is dezelfde als mijn eerste methode alleen anders geschreven.
Het probleem is dat je niet alle competenties pakt die niet in werkgroep 2 zitten, maar sowieso elke competentie die niet in alle werkgroepen voorkomt.

[Dido]

Volgens mij kan het ook zonder subquery, met alleen een join...
Zoiets?

SQL:

select naam
from competentie cp
left outer join competentie_werkgroep wg
on cp.cp_id = wg.cp_id and wg.wg_id = '4'
where isnull(wg.cp_id)

[Guillome]

Die isnull werkt niet op die plek.

Als ik het zo doe pakt ie geen enkele competentie.

SQL:

select cp.naam, wg.competentie_ID, cp.nummer
from cgl_competenties as cp
left outer join cgl_competentie_werkproces as wg
on cp.competentie_ID = wg.competentie_ID and wg.werkproces_ID = '4'
where wg.competentie_ID = NULL

[Dido]

En wat laat ie zien zonder die where?

[Guillome]

SQL:

select cp.naam, wg.competentie_ID, cp.nummer
from cgl_competenties as cp
left outer join cgl_competentie_werkproces as wg
on cp.competentie_ID = wg.competentie_ID and wg.werkproces_ID = '4'

code:

Beslissen en activiteiten initieren  NULL   A   
Samenwerken en overleggen NULL  E   
Ethisch en integer handelen NULL    F   
Overtuigen en beinvloeden   4   H   
Formuleren en rapporteren   5   J   
Analyseren   6  M   
Plannen en organiseren   7  Q

Zo werkt het ook, moet ik alleen in PHP nog verder filteren
Als ik where competentie_ID = NULL doe geeft ie een foutmelding:
Ambiguous column name 'competentie_ID'.

[ Voor 5% gewijzigd door Guillome op 27-06-2007 13:17 ]

[RobIII]

Guillome schreef op woensdag 27 juni 2007 @ 13:06:

SQL:

select cp.naam, wg.competentie_ID, cp.nummer
where wg.competentie_ID = NULL

Iets is nooit gelijk aan NULL; je zult sowieso dus "blahblah is NULL" moeten gebruiken. Probeer maar eens:

SQL:

select case when NULL is NULL then 'jep' else 'nope' end
select case when NULL = NULL then 'jep' else 'nope' end

Output:

code:

jep
nope

[ Voor 23% gewijzigd door RobIII op 27-06-2007 13:18 ]

[BasieP]

Als ik where competentie_ID = NULL doe geeft ie een foutmelding:
Ambiguous column name 'competentie_ID'.

de error die je krijgt betekent dat die colomn in meerdere tabellen voorkomt. dus ff iets er voor zetten wat de tabel specificeerd

[ Voor 40% gewijzigd door BasieP op 27-06-2007 13:19 ]

[Dido]

Guillome schreef op woensdag 27 juni 2007 @ 13:13:
Zo werkt het ook, moet ik alleen in PHP nog verder filteren
Als ik where competentie_ID = NULL doe geeft ie een foutmelding:
Ambiguous column name 'competentie_ID'.

Hij doet het dus prima, en ik zette niet voor niets isnull(blaat) neer.
Dat die kolom ambiguous is, komt omdat ie in beide tabellen staat, je wilt controleren of ie in 1 van die twee tabellen niet bestaat. Mag je zelf bepalen welke

[Gonadan]

Dido schreef op woensdag 27 juni 2007 @ 13:31:
Mag je zelf bepalen welke

Het maakt wel degelijk uit welke hij kiest. Maar dat bedoel je ook waarschijnlijk.

Als ik where competentie_ID = NULL doe geeft ie een foutmelding:
Ambiguous column name 'competentie_ID'.

Dat soort fouten moet je toch echt zelf op kunnen lossen als je thuis bent in de sql hoor.

[Guillome]

bedankt voor de hulp jongens, het werkt al zoals ik al vermeld had