Results 1 to 3 of 3

Thread: repeated posts

  1. #1
    Join Date
    Sep 2003

    Unanswered: repeated posts

    I have a database where I collect student information from three different tables.
    When I write a select case to see all the student information, the problem is that a student can have more than onte contact person from ex AF. How can I see all this information as one record?

    I wrote like this:

    Select distinct Studieinfo.PersNR, Elev.Fornamn + ' ' + Elev.Efternamn AS Namn, Studieinfo.Startvecka, Studieinfo.slutvecka,
    Studieinfo.startdatum, Studieinfo.slutdatum, Studieinfo.Kursort, Studieinfo.Studietid, Studieinfo.Forlangning,
    Studieinfo.beraknad_studietid, Studieinfo.mal, Studieinfo.delrapport, Studieinfo.moduler,
    KontaktPersoner_FK.Fornamn + ' ' + KontaktPersoner_FK.Efternamn AS KontaktFK, KontaktPersoner_AF.Fornamn + ' ' +KontaktPersoner_AF.Efternamn AS KontaktAF
    From Studieinfo, KontaktPersoner_FK, Kontakt_FK, KontaktPersoner_AF, Kontakt_AF, Elev
    WHERE Elev.PersNR=Kontakt_FK.PersNR
    and Elev.PersNR=Kontakt_AF.PersNR
    and Elev.PersNR=Studieinfo.PersNR
    and Elev.PersNR='691215-3638'
    and Kontakt_FK.KontaktNR_FK=KontaktPersoner_FK.Kontakt NR_FK
    and Kontakt_AF.KontaktNR_AF=KontaktPersoner_AF.Kontakt NR_AF

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    If you have defined contact types (Mothe, Father, Guardian, ParoleOfficer...) then you can write a CROSSTAB query to do this. Look it up in Books Online for instructions. Otherwise you may need to use a cursor to loop through related records and concatenate multiple contact records into a single character string. A user-defined function would be ideal for this.

    I'd also say that this type of formatting (which is purely for the sake of appearance) is often best delegated to the reporting interface (crystal, VB, Excel, Access, ect...). In a sense, when you try to formulate a query like this you are asking a relational database to be non-relational.


  3. #3
    Join Date
    Sep 2003


    Thanks for your advice. I was actually thinking of correcting it within the asp on the page. Like you suggested but was just wondering if it was possible to do with the sql.

Posting Permissions

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