SQL problem

I am using VFP 6 and my problem is that SQL does not seem to allow two
inner joins from one table (Tenants) to two child tables (Lettings and
Tenacc)

The following code works as far as it goes, without Tenacc:-

SELECT Tenants.lastname, Lettings.lettingref, Lettings.curr_rent,;
Blocks.blockname, Units.garnum, Owners.owner;
FROM garages!tenants INNER JOIN garages!lettings;
INNER JOIN garages!units;
INNER JOIN garages!blocks;
INNER JOIN garages!owners ;
ON Blocks.ownerref = Owners.ownerref ;
ON Units.blockref = Blocks.blockref ;
ON Lettings.unitref = Units.unitref ;
ON Tenants.tenantref = Lettings.tenantref;
ORDER BY Tenants.lastname

What I want to do is to join another table Tenacc (with multiple entries
for each tenant) to Tenants on tenantref so that I can show against each
tenant details from the above select plus all the debits and credits for
the tenant from Tenacc.

Help please.
--
Tim Hobson

Trey
Thu Oct 06 10:47:43 CDT 2005

Reorder your joins - this means writing the code instead of using the
view or query designer.
Basically, change the pattern to be JOIN..ON..JOIN..ON instead of the
nested join syntax [which, imho, is hard to read anyway]

e.g..
...
from garages!tenants
inner join garages!lettings on tenants.tenantref = lettings.tenantref
inner join garages!units on lettings.unitref = units.unitref
etc..

Tim Hobson wrote:

>I am using VFP 6 and my problem is that SQL does not seem to allow two
>inner joins from one table (Tenants) to two child tables (Lettings and
>Tenacc)
>
>The following code works as far as it goes, without Tenacc:-
>
>SELECT Tenants.lastname, Lettings.lettingref, Lettings.curr_rent,;
> Blocks.blockname, Units.garnum, Owners.owner;
> FROM garages!tenants INNER JOIN garages!lettings;
> INNER JOIN garages!units;
> INNER JOIN garages!blocks;
> INNER JOIN garages!owners ;
> ON Blocks.ownerref = Owners.ownerref ;
> ON Units.blockref = Blocks.blockref ;
> ON Lettings.unitref = Units.unitref ;
> ON Tenants.tenantref = Lettings.tenantref;
> ORDER BY Tenants.lastname
>
>What I want to do is to join another table Tenacc (with multiple entries
>for each tenant) to Tenants on tenantref so that I can show against each
>tenant details from the above select plus all the debits and credits for
>the tenant from Tenacc.
>
>Help please.
>
>

Tim
Fri Oct 07 08:37:26 CDT 2005

Thanks. I agree that this is better coding, but I am afraid it still
produces the same result. I am still unable to add the further join of
:

Garages.tenants inner join garages.tenacc on
tenants.tenantref=tenacc.tenantref.

The problem is that you cannot have two joins from tenants to two other
different tables.

Tim

In article <#d3DWzoyFHA.3312@TK2MSFTNGP09.phx.gbl>, Trey Walpole
<treypoNOle@comSPAMcast.net> writes
>Reorder your joins - this means writing the code instead of using the
>view or query designer.
>Basically, change the pattern to be JOIN..ON..JOIN..ON instead of the
>nested join syntax [which, imho, is hard to read anyway]
>
>e.g..
>...
>from garages!tenants
>inner join garages!lettings on tenants.tenantref = lettings.tenantref
>inner join garages!units on lettings.unitref = units.unitref
>etc..
>
>Tim Hobson wrote:
>
>>I am using VFP 6 and my problem is that SQL does not seem to allow two
>>inner joins from one table (Tenants) to two child tables (Lettings and
>>Tenacc)
>>
>>The following code works as far as it goes, without Tenacc:-
>>
>>SELECT Tenants.lastname, Lettings.lettingref, Lettings.curr_rent,;
>> Blocks.blockname, Units.garnum, Owners.owner;
>> FROM garages!tenants INNER JOIN garages!lettings;
>> INNER JOIN garages!units;
>> INNER JOIN garages!blocks;
>> INNER JOIN garages!owners ;
>> ON Blocks.ownerref = Owners.ownerref ;
>> ON Units.blockref = Blocks.blockref ;
>> ON Lettings.unitref = Units.unitref ;
>> ON Tenants.tenantref = Lettings.tenantref;
>> ORDER BY Tenants.lastname
>>
>>What I want to do is to join another table Tenacc (with multiple entries
>>for each tenant) to Tenants on tenantref so that I can show against each
>>tenant details from the above select plus all the debits and credits for
>>the tenant from Tenacc.
>>
>>Help please.
>>

--
Tim Hobson

Cindy
Fri Oct 07 15:53:41 CDT 2005

Hi Tim,

You haven't said what's wrong with your result, but what you're asking for
is like an Access subreport. To do a subreport in VFP6 use the technique
outlined at http://fox.wikis.com/wc.dll?Wiki~MultiChildFoxProReports.


--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden


"Tim Hobson" <Tim@tjhobson.demon.co.uk> wrote in message
news:ghntepBWonRDFwJv@tjhobson.demon.co.uk...
> Thanks. I agree that this is better coding, but I am afraid it still
> produces the same result. I am still unable to add the further join of :
>
> Garages.tenants inner join garages.tenacc on
> tenants.tenantref=tenacc.tenantref.
>
> The problem is that you cannot have two joins from tenants to two other
> different tables.
>
> Tim
>
> In article <#d3DWzoyFHA.3312@TK2MSFTNGP09.phx.gbl>, Trey Walpole
> <treypoNOle@comSPAMcast.net> writes

>>>I am using VFP 6 and my problem is that SQL does not seem to allow two
>>>inner joins from one table (Tenants) to two child tables (Lettings and
>>>Tenacc)

>>>What I want to do is to join another table Tenacc (with multiple entries
>>>for each tenant) to Tenants on tenantref so that I can show against each
>>>tenant details from the above select plus all the debits and credits for
>>>the tenant from Tenacc.

Tim
Sat Oct 08 11:52:06 CDT 2005

Thanks, Cindy.

I have printed out your reference, which seems to use CREATE CURSOR
without SQL. In my case it would probably mean scanning 6 tables to get
the relationships right. I have not tried it yet because of the number
of tables involved.

To explain the problem more thoroughly. The following code is OK and
will give me 3 results, one for each of the three lettings in the name
of Hicks and including the correct blocknames, garnums, and owners.

SELECT tenants.tenantref,tenants.lastname,blocks.blockname,
units.garnum,owners.owner;
FROM garages!tenants;
INNER JOIN garages!lettings ON Tenants.tenantref =
Lettings.tenantref;
INNER JOIN garages!units ON Lettings.unitref = Units.unitref
;
INNER JOIN garages!blocks ON units.blockref
=blocks.blockref ;
INNER JOIN garages!owners ON Blocks.ownerref =owners.ownerref;
WHERE Lettings.current = .T.;
.AND. blockname='Blendon Road';
.AND. lastname='Hicks';
ORDER BY blocks.blockname,units.garnum;
into cursor tempsql
browse

Tenacc for Hicks contains 3 debit entries each month (what he owes for
each letting) and one credit entry for his combined payment. It is
related to tenants on tenantref.

I now want to add tenacc debits and credits and try the following:

SELECT tenants.tenantref,tenants.lastname,blocks.blockname;
units.garnum,owners.owner,tenacc.debit,tenacc.credit,tenacc.date;
FROM garages!tenants;
INNER JOIN garages!tenacc ON garages.tenantref =
tenacc.tenantref;
INNER JOIN garages!lettings ON Tenants.tenantref =
Lettings.tenantref;
INNER JOIN garages!units ON Lettings.unitref = Units.unitref
;
INNER JOIN garages!blocks ON units.blockref
=blocks.blockref ;
INNER JOIN garages!owners ON Blocks.ownerref =owners.ownerref;
WHERE Lettings.current = .T.;
.AND. blockname='Blendon Road';
.AND. lastname='Hicks';
ORDER BY blocks.blockname,units.garnum,tenacc.date;
into cursor tempsql
browse

The result duplicates all the debits and credits against each letting
instead of just showing them against each tenant, in this case three
times. I have to go via the relationships from tenants to owners in
order to get blockname, garnum, and owner. Otherwise a simple join of
tenants and tenacc would do the job.

Could I create a SQL cursor as in the first coding above, and then join
that cursor in some way to tenacc ? I have considered doing it like that
through a temporary table instead of a cursor, which I could delete at
the end.

Sorry to make this sound so complicated, but I am a bit desperate as I
need to be able to print it all out to produce the necessary accounting
data. Once I have the correct SQL data I will put it into a report.

Tim


In article <eCv2PG4yFHA.2652@TK2MSFTNGP14.phx.gbl>, Cindy Winegarden
<cindy_winegarden@msn.com> writes
>Hi Tim,
>
>You haven't said what's wrong with your result, but what you're asking for
>is like an Access subreport. To do a subreport in VFP6 use the technique
>outlined at http://fox.wikis.com/wc.dll?Wiki~MultiChildFoxProReports.
>
>

--
Tim Hobson

Cindy
Sat Oct 08 13:04:35 CDT 2005

"Tim Hobson" <Tim@tjhobson.demon.co.uk> wrote in message
news:Q5vhYbC2k$RDFwOx@tjhobson.demon.co.uk...

Hi Tim

> To explain the problem more thoroughly. The following code is OK and
> will give me 3 results, one for each of the three lettings in the name
> of Hicks and including the correct blocknames, garnums, and owners.....


> Tenacc for Hicks contains 3 debit entries each month (what he owes for
> each letting) and one credit entry for his combined payment. It is
> related to tenants on tenantref.....


> The result duplicates all the debits and credits against each letting
> instead of just showing them against each tenant, in this case three
> times. ......
>
> Could I create a SQL cursor as in the first coding above, and then join
> that cursor in some way to tenacc ?

You can always do SQL in two steps, but how exactly do you expect the cursor
to look when you've got several debit/credit items for Hicks and you've got
several lettings for Hicks. It looks like you want:

Tenant Letting LettingDetails
Hicks 1 House#1
Hicks 2 House#1

And you also want

Tenant Debit_Credit
Hicks $200
Hicks -350

There is _absolutely no way_ to join data like the above without duplicating
the payments, which you don't want. You will have to use the multi-child
approach, or switch to VFP9 where improvements to the report engine allow
you to do subreports.


> I have considered doing it like that
> through a temporary table instead of a cursor, which I could delete at
> the end.

A cursor _is_ a temporary table, although it's in some temp folder and has a
funny name you don't see. The advantage is that Fox takes care of the
cleanup for you if you don't do it yourself.


> Sorry to make this sound so complicated, but I am a bit desperate as I
> need to be able to print it all out to produce the necessary accounting
> data. Once I have the correct SQL data I will put it into a report.

This type of report _is_ a little complicated. Outside of upgrading to VFP9
your only choice is to use the multi-child approach.


--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

Leonid
Sun Oct 09 07:55:40 CDT 2005

It's hard to verify it without your data, but may be this will give you
better result:

SELECT tenants.tenantref,tenants.lastname,blocks.blockname,
units.garnum,owners.owner;
FROM garages!tenants;
INNER JOIN garages!lettings ON Tenants.tenantref
=Lettings.tenantref;
.AND. Lettings.current = .T.;
INNER JOIN garages!units ON Lettings.unitref = Units.unitref;
INNER JOIN garages!blocks ON units.blockref=blocks.blockref ;
.AND. blocks.blockname='Blendon Road';
INNER JOIN garages!owners ON Blocks.ownerref =owners.ownerref;
.AND. owners.lastname='Hicks';
ORDER BY blocks.blockname,units.garnum;
into cursor tempsql

Leonid


"Tim Hobson" <Tim@tjhobson.demon.co.uk> wrote in message
news:Q5vhYbC2k$RDFwOx@tjhobson.demon.co.uk...
> Thanks, Cindy.
>
> I have printed out your reference, which seems to use CREATE CURSOR
> without SQL. In my case it would probably mean scanning 6 tables to get
> the relationships right. I have not tried it yet because of the number
> of tables involved.
>
> To explain the problem more thoroughly. The following code is OK and
> will give me 3 results, one for each of the three lettings in the name
> of Hicks and including the correct blocknames, garnums, and owners.
>
> SELECT tenants.tenantref,tenants.lastname,blocks.blockname,
> units.garnum,owners.owner;
> FROM garages!tenants;
> INNER JOIN garages!lettings ON Tenants.tenantref =
> Lettings.tenantref;
> INNER JOIN garages!units ON Lettings.unitref = Units.unitref
> ;
> INNER JOIN garages!blocks ON units.blockref
> =blocks.blockref ;
> INNER JOIN garages!owners ON Blocks.ownerref =owners.ownerref;
> WHERE Lettings.current = .T.;
> .AND. blockname='Blendon Road';
> .AND. lastname='Hicks';
> ORDER BY blocks.blockname,units.garnum;
> into cursor tempsql
> browse
>
> Tenacc for Hicks contains 3 debit entries each month (what he owes for
> each letting) and one credit entry for his combined payment. It is
> related to tenants on tenantref.
>
> I now want to add tenacc debits and credits and try the following:
>
> SELECT tenants.tenantref,tenants.lastname,blocks.blockname;
> units.garnum,owners.owner,tenacc.debit,tenacc.credit,tenacc.date;
> FROM garages!tenants;
> INNER JOIN garages!tenacc ON garages.tenantref =
> tenacc.tenantref;
> INNER JOIN garages!lettings ON Tenants.tenantref =
> Lettings.tenantref;
> INNER JOIN garages!units ON Lettings.unitref = Units.unitref
> ;
> INNER JOIN garages!blocks ON units.blockref
> =blocks.blockref ;
> INNER JOIN garages!owners ON Blocks.ownerref =owners.ownerref;
> WHERE Lettings.current = .T.;
> .AND. blockname='Blendon Road';
> .AND. lastname='Hicks';
> ORDER BY blocks.blockname,units.garnum,tenacc.date;
> into cursor tempsql
> browse
>
> The result duplicates all the debits and credits against each letting
> instead of just showing them against each tenant, in this case three
> times. I have to go via the relationships from tenants to owners in
> order to get blockname, garnum, and owner. Otherwise a simple join of
> tenants and tenacc would do the job.
>
> Could I create a SQL cursor as in the first coding above, and then join
> that cursor in some way to tenacc ? I have considered doing it like that
> through a temporary table instead of a cursor, which I could delete at
> the end.
>
> Sorry to make this sound so complicated, but I am a bit desperate as I
> need to be able to print it all out to produce the necessary accounting
> data. Once I have the correct SQL data I will put it into a report.
>
> Tim
>
>
> In article <eCv2PG4yFHA.2652@TK2MSFTNGP14.phx.gbl>, Cindy Winegarden
> <cindy_winegarden@msn.com> writes
>>Hi Tim,
>>
>>You haven't said what's wrong with your result, but what you're asking for
>>is like an Access subreport. To do a subreport in VFP6 use the technique
>>outlined at http://fox.wikis.com/wc.dll?Wiki~MultiChildFoxProReports.
>>
>>
>
> --
> Tim Hobson

Tim
Sun Oct 09 03:40:08 CDT 2005

Thanks again, Cindy

I was afraid that what I wanted could not be done so I must now try a
little lateral thinking.

Perhaps create a new table which will avoid the duplicate from multiple
lettings by having separate field for each letting against only one
record for a tenant. I could do this fairly easily, and either just zap
and refill the table when I need to produce the report, or just delete
the table and re-create it when necessary. That could then be linked
successfully to tenacc.

The reason for considering a table instead of a cursor is that I can
manipulate a table more easily (I mean I know how to do it) by deleting
some records and packing or adding and filling fields after it has been
created.

It almost makes it look as if the original design of the database could
be better, but it works in almost every other way and I want to avoid
duplicate fields in different tables otherwise why have a relational
database atall ?

In article <OVhuOPDzFHA.2960@tk2msftngp13.phx.gbl>, Cindy Winegarden
<cindy_winegarden@msn.com> writes
>"Tim Hobson" <Tim@tjhobson.demon.co.uk> wrote in message
>news:Q5vhYbC2k$RDFwOx@tjhobson.demon.co.uk...
>
>Hi Tim
>
>> To explain the problem more thoroughly. The following code is OK and
>> will give me 3 results, one for each of the three lettings in the name
>> of Hicks and including the correct blocknames, garnums, and owners.....
>
>
>> Tenacc for Hicks contains 3 debit entries each month (what he owes for
>> each letting) and one credit entry for his combined payment. It is
>> related to tenants on tenantref.....
>
>
>> The result duplicates all the debits and credits against each letting
>> instead of just showing them against each tenant, in this case three
>> times. ......
>>
>> Could I create a SQL cursor as in the first coding above, and then join
>> that cursor in some way to tenacc ?
>
>You can always do SQL in two steps, but how exactly do you expect the cursor
>to look when you've got several debit/credit items for Hicks and you've got
>several lettings for Hicks. It looks like you want:
>
>Tenant Letting LettingDetails
>Hicks 1 House#1
>Hicks 2 House#1
>
>And you also want
>
>Tenant Debit_Credit
>Hicks $200
>Hicks -350
>
>There is _absolutely no way_ to join data like the above without duplicating
>the payments, which you don't want. You will have to use the multi-child
>approach, or switch to VFP9 where improvements to the report engine allow
>you to do subreports.
>
>
>> I have considered doing it like that
>> through a temporary table instead of a cursor, which I could delete at
>> the end.
>
>A cursor _is_ a temporary table, although it's in some temp folder and has a
>funny name you don't see. The advantage is that Fox takes care of the
>cleanup for you if you don't do it yourself.
>
>
>> Sorry to make this sound so complicated, but I am a bit desperate as I
>> need to be able to print it all out to produce the necessary accounting
>> data. Once I have the correct SQL data I will put it into a report.
>
>This type of report _is_ a little complicated. Outside of upgrading to VFP9
>your only choice is to use the multi-child approach.
>
>

--
Tim Hobson

Tim
Mon Oct 10 10:54:38 CDT 2005

Thanks, Leonid, but this does not work either.

Cindy has put her finger on it - what I want cannot be done from the
present related tables.

The only solution seems to be to duplicate a few fields from child
tables into tenants which I know is not good practice. Then I can show
what I want without having the join to lettings which is the cause of
the problem when one tenant has multiple lettings. It works like a charm
for the simple case of a tenant having a single letting.

Tim

In article <#tm01CNzFHA.1264@tk2msftngp13.phx.gbl>, Leonid
<leonid@NOgradaSPAM.lv> writes
>It's hard to verify it without your data, but may be this will give you
>better result:
>
>SELECT tenants.tenantref,tenants.lastname,blocks.blockname,
>units.garnum,owners.owner;
>FROM garages!tenants;
> INNER JOIN garages!lettings ON Tenants.tenantref
>=Lettings.tenantref;
> .AND. Lettings.current = .T.;
> INNER JOIN garages!units ON Lettings.unitref = Units.unitref;
> INNER JOIN garages!blocks ON units.blockref=blocks.blockref ;
> .AND. blocks.blockname='Blendon Road';
> INNER JOIN garages!owners ON Blocks.ownerref =owners.ownerref;
> .AND. owners.lastname='Hicks';
> ORDER BY blocks.blockname,units.garnum;
> into cursor tempsql
>
>Leonid
>
>
>"Tim Hobson" <Tim@tjhobson.demon.co.uk> wrote in message
>news:Q5vhYbC2k$RDFwOx@tjhobson.demon.co.uk...
>> Thanks, Cindy.
>>
>> I have printed out your reference, which seems to use CREATE CURSOR
>> without SQL. In my case it would probably mean scanning 6 tables to get
>> the relationships right. I have not tried it yet because of the number
>> of tables involved.
>>
>> To explain the problem more thoroughly. The following code is OK and
>> will give me 3 results, one for each of the three lettings in the name
>> of Hicks and including the correct blocknames, garnums, and owners.
>>
>> SELECT tenants.tenantref,tenants.lastname,blocks.blockname,
>> units.garnum,owners.owner;
>> FROM garages!tenants;
>> INNER JOIN garages!lettings ON Tenants.tenantref =
>> Lettings.tenantref;
>> INNER JOIN garages!units ON Lettings.unitref = Units.unitref
>> ;
>> INNER JOIN garages!blocks ON units.blockref
>> =blocks.blockref ;
>> INNER JOIN garages!owners ON Blocks.ownerref =owners.ownerref;
>> WHERE Lettings.current = .T.;
>> .AND. blockname='Blendon Road';
>> .AND. lastname='Hicks';
>> ORDER BY blocks.blockname,units.garnum;
>> into cursor tempsql
>> browse
>>
>> Tenacc for Hicks contains 3 debit entries each month (what he owes for
>> each letting) and one credit entry for his combined payment. It is
>> related to tenants on tenantref.
>>
>> I now want to add tenacc debits and credits and try the following:
>>
>> SELECT tenants.tenantref,tenants.lastname,blocks.blockname;
>> units.garnum,owners.owner,tenacc.debit,tenacc.credit,tenacc.date;
>> FROM garages!tenants;
>> INNER JOIN garages!tenacc ON garages.tenantref =
>> tenacc.tenantref;
>> INNER JOIN garages!lettings ON Tenants.tenantref =
>> Lettings.tenantref;
>> INNER JOIN garages!units ON Lettings.unitref = Units.unitref
>> ;
>> INNER JOIN garages!blocks ON units.blockref
>> =blocks.blockref ;
>> INNER JOIN garages!owners ON Blocks.ownerref =owners.ownerref;
>> WHERE Lettings.current = .T.;
>> .AND. blockname='Blendon Road';
>> .AND. lastname='Hicks';
>> ORDER BY blocks.blockname,units.garnum,tenacc.date;
>> into cursor tempsql
>> browse
>>
>> The result duplicates all the debits and credits against each letting
>> instead of just showing them against each tenant, in this case three
>> times. I have to go via the relationships from tenants to owners in
>> order to get blockname, garnum, and owner. Otherwise a simple join of
>> tenants and tenacc would do the job.
>>
>> Could I create a SQL cursor as in the first coding above, and then join
>> that cursor in some way to tenacc ? I have considered doing it like that
>> through a temporary table instead of a cursor, which I could delete at
>> the end.
>>
>> Sorry to make this sound so complicated, but I am a bit desperate as I
>> need to be able to print it all out to produce the necessary accounting
>> data. Once I have the correct SQL data I will put it into a report.
>>
>> Tim
>>
>>
>> In article <eCv2PG4yFHA.2652@TK2MSFTNGP14.phx.gbl>, Cindy Winegarden
>> <cindy_winegarden@msn.com> writes
>>>Hi Tim,
>>>
>>>You haven't said what's wrong with your result, but what you're asking for
>>>is like an Access subreport. To do a subreport in VFP6 use the technique
>>>outlined at http://fox.wikis.com/wc.dll?Wiki~MultiChildFoxProReports.
>>>
>>>
>>
>> --
>> Tim Hobson
>
>

--
Tim Hobson