Access to SQL

23 Sep 2005 2:37 AM

Patrice

Hello, How can I turn the following Access query into a SQL query?
Every time I try, I get Cartesian product

SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1,
dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2,
dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.ADDRESS2, dbo_ADDRESS.ADDRESS3,
dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIPCODE, dbo_ADDRESS.COUNTRY
FROM ((QNameAddress1 INNER JOIN dbo_CLIENT ON QNameAddress1.CLIENT_NUMBER =
dbo_CLIENT.CLIENT_NUMBER) INNER JOIN dbo_ADDRXREF ON
(QNameAddress1.MaxOfPOLICY_DATE_TIME = dbo_ADDRXREF.POLICY_DATE_TIME) AND
(QNameAddress1.POLICY_NUMBER = dbo_ADDRXREF.POLICY_NUMBER)) INNER JOIN
dbo_ADDRESS ON (dbo_ADDRXREF.SEQUENCE_NUMBER = dbo_ADDRESS.SEQUENCE_NUMBER)
AND (dbo_CLIENT.CLIENT_NUMBER = dbo_ADDRESS.CLIENT_NUMBER)
WHERE (((dbo_ADDRXREF.ADDR_USAGE)="1"));

23 Sep 2005 7:19 AM

John Bell

Hi

This may be a data issue

Your query looks fine:
SELECT    C.CLIENT_NUMBER,
    C.LNAME1,
    C.FNAME1,
    C.INIT1,
    C.LNAME2,
    C.FNAME2,
    C.INIT2,
    A.ADDRESS1,
    A.ADDRESS2,
    A.ADDRESS3,
    A.CITY,
    A.STATE,
    A.ZIPCODE,
    A.COUNTRY
FROM     QNameAddress1 Q
JOIN    dbo_CLIENT C ON Q.CLIENT_NUMBER = C.CLIENT_NUMBER
JOIN    dbo_ADDRXREF X ON Q.MaxOfPOLICY_DATE_TIME = X.POLICY_DATE_TIME AND
Q.POLICY_NUMBER = X.POLICY_NUMBER
JOIN    dbo_ADDRESS A ON X.SEQUENCE_NUMBER = A.SEQUENCE_NUMBER AND
C.CLIENT_NUMBER = A.CLIENT_NUMBER
WHERE    A.ADDR_USAGE='1'

See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
example data (as insert statements). It may also be worth posting expected
output from sample data.

John

Show quote

"Patrice" wrote:

> Hello, How can I turn the following Access query into a SQL query?
> Every time I try, I get Cartesian product
>
>
> SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1,
> dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2,
> dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.ADDRESS2, dbo_ADDRESS.ADDRESS3,
> dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIPCODE, dbo_ADDRESS.COUNTRY
> FROM ((QNameAddress1 INNER JOIN dbo_CLIENT ON QNameAddress1.CLIENT_NUMBER =
> dbo_CLIENT.CLIENT_NUMBER) INNER JOIN dbo_ADDRXREF ON
> (QNameAddress1.MaxOfPOLICY_DATE_TIME = dbo_ADDRXREF.POLICY_DATE_TIME) AND
> (QNameAddress1.POLICY_NUMBER = dbo_ADDRXREF.POLICY_NUMBER)) INNER JOIN
> dbo_ADDRESS ON (dbo_ADDRXREF.SEQUENCE_NUMBER = dbo_ADDRESS.SEQUENCE_NUMBER)
> AND (dbo_CLIENT.CLIENT_NUMBER = dbo_ADDRESS.CLIENT_NUMBER)
> WHERE (((dbo_ADDRXREF.ADDR_USAGE)="1"));
>
>
>

23 Sep 2005 7:19 AM

Earl

Plug that into the Query Analyzer and you should find the problem. Most
likely some or all of your INNER JOINS should be OUTER.

Show quote

"Patrice" <Patr***@discussions.microsoft.com> wrote in message
news:0D946FD4-B400-482F-8510-1AA01E1432BD@microsoft.com...
> Hello, How can I turn the following Access query into a SQL query?
> Every time I try, I get Cartesian product
>
>
> SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1,
> dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2,
> dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.ADDRESS2, dbo_ADDRESS.ADDRESS3,
> dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIPCODE,
> dbo_ADDRESS.COUNTRY
> FROM ((QNameAddress1 INNER JOIN dbo_CLIENT ON QNameAddress1.CLIENT_NUMBER
> =
> dbo_CLIENT.CLIENT_NUMBER) INNER JOIN dbo_ADDRXREF ON
> (QNameAddress1.MaxOfPOLICY_DATE_TIME = dbo_ADDRXREF.POLICY_DATE_TIME) AND
> (QNameAddress1.POLICY_NUMBER = dbo_ADDRXREF.POLICY_NUMBER)) INNER JOIN
> dbo_ADDRESS ON (dbo_ADDRXREF.SEQUENCE_NUMBER =
> dbo_ADDRESS.SEQUENCE_NUMBER)
> AND (dbo_CLIENT.CLIENT_NUMBER = dbo_ADDRESS.CLIENT_NUMBER)
> WHERE (((dbo_ADDRXREF.ADDR_USAGE)="1"));
>
>
>