1. Registered User
Join Date
Sep 2012
Posts
4

Hello

I have 2 Tables named: Docent and Vakdocent

The table Docent contain the following Fields
- docentnr
- Forename
- Lastname

The table Vakdocent contain the following Fields
- docentnr
- vakcode

Vakcode contains the Subjects the person is giving
Docentnr is a number which is linked to a person.

Docentnr 2 has three vakcodes and Docentnr 10 has 2 vakcodes
But 1 subject(vakcode) can be given by docentnr 2 and by docentnr 10

So I have to written a query and the answer should be that 1 subject which can be given by nr 2 and by nr 10.

How can I compare that?

Thank you for helping me!

2. Registered User
Join Date
Apr 2012
Posts
213
Hi,

I do not know if I understand correctly, but try something like this:

Code:
```with CTE as
(
select vakcode from Vakdocent
group by vakcode
having count(*) > 1
)

select *
from CTE as c
join Vakdocent as v on v.vakcode = c.vakcode
join Docent as d on d.docentnr = v.docentnr```
Hope it's useful.

3. Registered User
Join Date
Sep 2012
Posts
4
Haha now I get confused.

I thought something like this image.
But at this point I get all the Subjects that were given by docentnr 2 OR docent nr 10.

But now I need the subjects that were given by docentnr 2 AND docentnr 10.

4. Registered User
Join Date
Apr 2012
Posts
213
Try:

Code:
```select *
from (select vakcode from Vakdocent
where docentnr = 2 or docentnr = 10
group by vakcode
having count(*) > 1) as c
join Vakdocent as v on v.vakcode = c.vakcode
join Docent as d on d.docentnr = v.docentnr```
Hope this helps.

5. Registered User
Join Date
Sep 2012
Posts
4
Thank you for helping me in the right direction but I have still a few questions:
On the image you see there are 2 colums with vakcode and 2 colums with docent why is that?
And you see also that docentnr 1 and docentnr 6 are viewed and it is true they give that subject but I want only from docentnr 2 and docentnr 10.
Can you explain in short the code you give to me?

Thank you!

6. Registered User
Join Date
Apr 2012
Posts
213
Try:

Code:
```select d.*
from (select vakcode from Vakdocent
where docentnr = 2 or docentnr = 10
group by vakcode
having count(*) > 1) as c
join Vakdocent as v on v.vakcode = c.vakcode
join Docent as d on d.docentnr = v.docentnr
where v.docentnr = 2 or v.docentnr = 10```
Hope this helps.

7. Registered User
Join Date
Sep 2012
Posts
4
Thank you!
It works finally!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•