Tough Sql Query problem involving family relationsships

Question

i have a many to many table relationship that involves 2 logical tables.

Record table that joins to a relation table on primaryID Second instance of record table that joins to the relation table on ReciprocalID

The purpose of this is to show family relations within the database. Each primary Record table has one or more rows in the relationtable that shows everyother family relationship this person has in the database.

I have been tasked with trying to make a contact list that involves displaying the names of each of the children that attend this school along with their parents and contact information.

I have gotten to a point where I am able to show the children under each parent, but now I have to find a way to merge these together.

Since I have no control over the design of this database(its Education Edge 7) I have made a separate database that holds my queries and views for my reports. The school I am doing this work for only has access to CR 8.5.

Right now I have my top group in CR as the lastname of the recordstable, my second group is on the fullname of the recordstable. I have a subreport that pulls in all the child records.

I have used a case when statement in my primary view(the one described above) to convert 'daughter' and 'son' to child and 'mother' or 'father' to parent.

hopefully this hasnt rambled too much. If you need anymore information just ask.

SELECT     dbo.vwEA7RelationshipsTableView.PRIMARYID,
       dbo.vwEA7RecordsTableView.LASTNAME AS PRIMARYLASTNAME,
      dbo.vwEA7RecordsTableView.FIRSTNAME AS PRIMARYFIRSTNAME,
       dbo.vwEA7RecordsTableView.NAMEFORDISPLAY AS PRIMARYNAME,
             CASE dbo.vwEA7RelationshipsTableView.PRIMARYDESC 
       WHEN 'Father' THEN 'Parent' 
       WHEN 'Mother' THEN 'Parent' 
       WHEN 'Son' THEN 'Child'
        WHEN'Daughter' THEN 'Child'
        ELSE dbo.vwEA7RelationshipsTableView.PRIMARYDESC 
       END AS PRIMARYDESC,
      dbo.vwEA7RelationshipsTableView.RELATIONID,
      vwEA7RecordsTableView_1.LASTNAME AS RELATIONLASTNAME,
      vwEA7RecordsTableView_1.NAMEFORDISPLAY AS RELATIONNAME,
       dbo.vwEA7RelationshipsTableView.RELATIONDESC
FROM         dbo.vwEA7RelationshipsTableView INNER JOIN
                      dbo.vwEA7RecordsTableView ON
          dbo.vwEA7RelationshipsTableView.PRIMARYID = dbo.vwEA7RecordsTableView.ID INNER JOIN
                      dbo.vwEA7RecordsTableView AS vwEA7RecordsTableView_1 ON
          dbo.vwEA7RelationshipsTableView.RELATIONID = vwEA7RecordsTableView_1.ID

TableViews are really just recreation of the primary tables from the main database.

Answer 1

And your question is ?

EDIT

Sorry, overlooked the remark that was already there. PLease ignore.

Answer 2

I have solved this issue. My sql code was good, it was a matter of formatting my internal paramaters for Crystal as well as some creative grouping.