SQL Problem

[Paul Summerfield]

I am having a problem with this SQL :-

SELECT Claims_main.cl_pk, Claims_main.cl_sc_fk, Claims_main.cl_ag_fk,;
Claims_main.cl_in_fk, Country.cy_name,schemes.sc_name,;
Claims_payments.py_paid, Claims_payments.py_paiddate,;
Claims_payments.py_amount, Claims_payments.py_pdate,;
Claims_payments.py_in_fk, Claims_payments.py_cl_fk,;
Claims_payments.py_sc_fk, Claims_payments.py_pk,;
Claims_payments.py_section,Claims_main.cl_cy_fk;
FROM tg!claims_payments INNER JOIN tg!claims_main;
INNER JOIN tg!country;
INNER JOIN tg!schemes;
ON Claims_main.cl_sc_fk = schemes.sc_pk;
ON Country.cy_pk = Claims_main.cl_cy_fk ;
ON Claims_payments.py_cl_fk = Claims_main.cl_pk;
&lcFilter;
INTO CURSOR C_Report


I get all the records i need but the scheme table does not shopw the relationship
it just shows the first record.

It seems that as soon as i add the 4th table the relationship does not work.
If i change them round anf have a say my Country table as the forth table
then that does not relate and all the other do. ?

What am i doing wrong.

May thanks

Paul Summerfield

[Barbara Peisch]

Your problem is likely to be in the condition in your lcFilter variable, but
since you haven't posted the contents of that variable, it's impossible for
anywhere here to tell.

Also, please do not use the syntax you've posted for doing your joins! It's
hard to read and gets worse the more tables you add. I know this is what
the Query Designer produces, but the QD is very limited, and this is just
one reason most of us don't use it. Instead, write your joins as follows:

FROM tg!claims_payments INNER JOIN tg!claims_main;
ON Claims_payments.py_cl_fk = Claims_main.cl_pk;
INNER JOIN tg!country;
ON Country.cy_pk = Claims_main.cl_cy_fk ;
INNER JOIN tg!schemes;
ON Claims_main.cl_sc_fk = schemes.sc_pk;


-BP
www.peisch.com

FoxPro Info: http://msdn.microsoft.com/vfoxpro
FoxPro News: http://foxcentral.net


"Paul Summerfield" <paul@tpgplc.co.uk> wrote in message
news:3d36bcf1$1@10.1.10.29...
>
> I am having a problem with this SQL :-
>
> SELECT Claims_main.cl_pk, Claims_main.cl_sc_fk, Claims_main.cl_ag_fk,;
> Claims_main.cl_in_fk, Country.cy_name,schemes.sc_name,;
> Claims_payments.py_paid, Claims_payments.py_paiddate,;
> Claims_payments.py_amount, Claims_payments.py_pdate,;
> Claims_payments.py_in_fk, Claims_payments.py_cl_fk,;
> Claims_payments.py_sc_fk, Claims_payments.py_pk,;
> Claims_payments.py_section,Claims_main.cl_cy_fk;
> FROM tg!claims_payments INNER JOIN tg!claims_main;
> INNER JOIN tg!country;
> INNER JOIN tg!schemes;
> ON Claims_main.cl_sc_fk = schemes.sc_pk;
> ON Country.cy_pk = Claims_main.cl_cy_fk ;
> ON Claims_payments.py_cl_fk = Claims_main.cl_pk;
> &lcFilter;
> INTO CURSOR C_Report
>
>
> I get all the records i need but the scheme table does not shopw the
relationship
> it just shows the first record.
>
> It seems that as soon as i add the 4th table the relationship does not
work.
> If i change them round anf have a say my Country table as the forth table
> then that does not relate and all the other do. ?
>
> What am i doing wrong.
>
> May thanks
>
> Paul Summerfield
>

[Tamar E. Granor]

On 18 Jul 2002 06:04:49 -0700, "Paul Summerfield" <paul@tpgplc.co.uk>
wrote:

>
>I am having a problem with this SQL :-
>
>SELECT Claims_main.cl_pk, Claims_main.cl_sc_fk, Claims_main.cl_ag_fk,;
> Claims_main.cl_in_fk, Country.cy_name,schemes.sc_name,;
> Claims_payments.py_paid, Claims_payments.py_paiddate,;
> Claims_payments.py_amount, Claims_payments.py_pdate,;
> Claims_payments.py_in_fk, Claims_payments.py_cl_fk,;
> Claims_payments.py_sc_fk, Claims_payments.py_pk,;
> Claims_payments.py_section,Claims_main.cl_cy_fk;
> FROM tg!claims_payments INNER JOIN tg!claims_main;
> INNER JOIN tg!country;
> INNER JOIN tg!schemes;
> ON Claims_main.cl_sc_fk = schemes.sc_pk;
> ON Country.cy_pk = Claims_main.cl_cy_fk ;
> ON Claims_payments.py_cl_fk = Claims_main.cl_pk;
> &lcFilter;
> INTO CURSOR C_Report
>
>

To add to what Barbara said, the problem with this query is that the
join conditions aren't in the right order. As written, this query says
to:

1) join Schemes and Country on the condition Claims_main.cl_sc_fk =
schemes.sc_pk
2) join that result to Claims_Main on the condition Country.cy_pk =
Claims_main.cl_cy_fk
3) join that result to Claims_Payments on the condition
Claims_payments.py_cl_fk = Claims_main.cl_pk

In general, when you use this syntax for a query, the last two tables
listed are joined first using the first condition shown. Then, the
joins work their way outward from the middle.

The sequential syntax Barbara showed you is far more readable and
easier to get right.

Tamar