Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2012
    Posts
    4

    Unanswered: SQL Query Problem

    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. #2
    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. #3
    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.
    Attached Thumbnails Attached Thumbnails answer.png  

  4. #4
    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. #5
    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!
    Attached Thumbnails Attached Thumbnails Naamloos.png  

  6. #6
    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. #7
    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
  •