difficult query

[cookie720]

Hi all,
Im trying to display some client details in a pdf of attendances. My table structure is Clients, Matters, Attendances.
Clients can have one to many matters
Matters can have one to many attendances.
I want to see which client is related to the attendance "through" the matters table

SELECT c.FirstName, c.LastName, c.PhoneNumber
FROM clients c
JOIN matters
USING ( ClientID )
JOIN attendances
USING ( MatterID )
When i chuck this __sql__ function into my attendance delegate, i get the following error:

Code: Select all

Fatal error: Failed parsing SQL query on select: SELECT c.FirstName,c.LastName FROM clients c JOIN matters USING ClientID) JOIN attendances USING MatterID . The Error was Parse error: Unexpected clause on line 4 JOIN matters USING ClientID) ^ found: "USING" in C:\wamp\www\live_matters\xataface\lib\SQL\Parser.php on line 1752

when i use the code in phpmyadmin, i do get results, but they are seemingly random: a list of 13 random first and last names of clients.

[Jean]

Did you try ?

Code: Select all

SELECT c.FirstName, c.LastName, c.PhoneNumber
FROM clients c
NATURAL LEFT JOIN matters
NATURAL LEFT JOIN  attendances


[cookie720]

almost!!!! im getting the following error:

Code: Select all

Fatal error: Error calculating the number of related records there are for the relationship 'attendances' in the table 'matters'. There was a problem performing the sql query 'SELECT COUNT(*) as num from `dataface__view_attendances_a646ee286fdc48a074f38bd9516e7fbd` as `attendances` where `attendances`.`MatterID` = '0620''. The MYSQL error returned was 'Unknown column 'attendances.MatterID' in 'where clause''.
On line 589 of file C:\wamp\www\live_matters\xataface\Dataface\Record.php in function printStackTrace()
On line 479 of file C:\wamp\www\live_matters\xataface\Dataface\Record.php in function numRelatedRecords(attendances,0)
On line 727 of file C:\wamp\www\live_matters\xataface\Dataface\Record.php in function _loadRelatedRecordBlock(attendances,0,0,0)
On line 3013 of file C:\wamp\www\live_matters\xataface\Dataface\Record.php in function getRelatedRecords(attendances,1,,,0,0)
On line 966 of file C:\wamp\www\live_matters\xataface\Dataface\Record.php in function Dataface_RelationshipIterator(Dataface in C:\wamp\www\live_matters\xataface\Dataface\Record.php on line 589


i have no idea what i did, but i messed around with the view tables in phpmyadmin a few times,(the ones that dataface creates on the fly) and somehow i managed to get client data print to my attendance pdf....but i went to my matter and had a similar error there.

p.s. i have that sql in my attendance delegate class

[shannah]

It is complaining that the MatterID column doesn't exist. Is this a new column that you have added? It is possible you need to clear the database views (this option is available in the "Control Panel". Upper right link on the page .. then Clear Views).

[cookie720]

Thanks guys, I no longer need to play around with sql because I just used the same code as shannah gave me in another thread to overcome this problem.

Code: Select all

[clients]
    matters.MatterID="$MatterID"
    clients.ClientID=matters.ClientID

Thanks again shannah!