SQL query not running (using Join)

SQL query not running (using Join)

Post by Ben Alice » Sat, 18 Mar 2000 04:00:00



I am running a query that involves 4 tables. The query sits like is locked
up, but it is not. I have gone to the MSDN web site and found a few articles
on this problem, and have tried the suggested solutions, but no dice.

Other notes:
- All the fields in the join statement are indexed
- All the filter conditions in the where clause are also indexed
- I am using Visual FoxPro 6.0 with SP3.

Any help or suggestions will be greatly appreciated.

The query I am trying is:

SELECT  <Field List>;
 FROM FORCE  CUSTOMER LEFT OUTER JOIN PACKID ;
     LEFT OUTER JOIN CCTRANS ;
     LEFT OUTER JOIN PAYMENT ;
    ON CUSTOMER.CCUSTID = PAYMENT.CCUSTID ;
    ON CUSTOMER.CCUSTID = CCTRANS.CCUSTID ;
    ON CUSTOMER.CCUSTID = PACKID.CCUSTID   ;
 WHERE   <filter condition> ;
 INTO TABLE <destination>

Thanks

Ben Alicea

 
 
 

SQL query not running (using Join)

Post by Anders Altber » Sat, 18 Mar 2000 04:00:00


Ben
JOIN syntax requires you to match up JOIN and ON clauses in the correct
order.
  FROM  Packid ;
    RIGHT OUTER JOIN Cctrans ;
    RIGHT OUTER JOIN Payment ;
    RIGHT JOIN Customers ;
    ON Customers.ccustid = Payment.ccustid ;
    ON Customers.ccustid = Cctrans.ccustid ;
    ON Customers.ccustid = Packid.ccustid  ;
    WHERE ..
-Anders


Quote:> I am running a query that involves 4 tables. The query sits like is locked
> up, but it is not. I have gone to the MSDN web site and found a few
articles
> on this problem, and have tried the suggested solutions, but no dice.

> Other notes:
> - All the fields in the join statement are indexed
> - All the filter conditions in the where clause are also indexed
> - I am using Visual FoxPro 6.0 with SP3.

> Any help or suggestions will be greatly appreciated.

> The query I am trying is:

> SELECT  <Field List>;
>  FROM FORCE  CUSTOMER LEFT OUTER JOIN PACKID ;
>      LEFT OUTER JOIN CCTRANS ;
>      LEFT OUTER JOIN PAYMENT ;
>     ON CUSTOMER.CCUSTID = PAYMENT.CCUSTID ;
>     ON CUSTOMER.CCUSTID = CCTRANS.CCUSTID ;
>     ON CUSTOMER.CCUSTID = PACKID.CCUSTID   ;
>  WHERE   <filter condition> ;
>  INTO TABLE <destination>

> Thanks

> Ben Alicea


 
 
 

SQL query not running (using Join)

Post by Craig Berntso » Sat, 18 Mar 2000 04:00:00


Try breaking it down into three SQL SELECT Statements, each joining 2 at a
time.

--

Craig Berntson
MCP, Microsoft FoxPro MVP
eSolutions Services, LLC
Salt Lake City Fox User Group
http://members.home.com/foxpro


Quote:> I am running a query that involves 4 tables. The query sits like is locked
> up, but it is not. I have gone to the MSDN web site and found a few
articles
> on this problem, and have tried the suggested solutions, but no dice.

> Other notes:
> - All the fields in the join statement are indexed
> - All the filter conditions in the where clause are also indexed
> - I am using Visual FoxPro 6.0 with SP3.

> Any help or suggestions will be greatly appreciated.

> The query I am trying is:

> SELECT  <Field List>;
>  FROM FORCE  CUSTOMER LEFT OUTER JOIN PACKID ;
>      LEFT OUTER JOIN CCTRANS ;
>      LEFT OUTER JOIN PAYMENT ;
>     ON CUSTOMER.CCUSTID = PAYMENT.CCUSTID ;
>     ON CUSTOMER.CCUSTID = CCTRANS.CCUSTID ;
>     ON CUSTOMER.CCUSTID = PACKID.CCUSTID   ;
>  WHERE   <filter condition> ;
>  INTO TABLE <destination>

> Thanks

> Ben Alicea

 
 
 

SQL query not running (using Join)

Post by Mark Moor » Fri, 31 Mar 2000 04:00:00


Anders is right, remember, in VFP on VFP finds a ON clause, if searchs back
to the LAST 2 tables that were list, in your case, the first on clause joins
customer & payment, however, the last two tables that VFP saw were payment &
ccTrans.  You can also writer you join syntax as followed:

FROM packid ;
    JOIN cctrans ;
        ON cctrans.ccustid = packid.ccustid ;
    JOIN payment ;
        ON payment.ccustid = cctrans.ccustid ;
    JOIN customers ;
        ON customer.ccustid = payment.ccustid


> Ben
> JOIN syntax requires you to match up JOIN and ON clauses in the correct
> order.
>   FROM  Packid ;
>     RIGHT OUTER JOIN Cctrans ;
>     RIGHT OUTER JOIN Payment ;
>     RIGHT JOIN Customers ;
>     ON Customers.ccustid = Payment.ccustid ;
>     ON Customers.ccustid = Cctrans.ccustid ;
>     ON Customers.ccustid = Packid.ccustid  ;
>     WHERE ..
> -Anders



> > I am running a query that involves 4 tables. The query sits like is
locked
> > up, but it is not. I have gone to the MSDN web site and found a few
> articles
> > on this problem, and have tried the suggested solutions, but no dice.

> > Other notes:
> > - All the fields in the join statement are indexed
> > - All the filter conditions in the where clause are also indexed
> > - I am using Visual FoxPro 6.0 with SP3.

> > Any help or suggestions will be greatly appreciated.

> > The query I am trying is:

> > SELECT  <Field List>;
> >  FROM FORCE  CUSTOMER LEFT OUTER JOIN PACKID ;
> >      LEFT OUTER JOIN CCTRANS ;
> >      LEFT OUTER JOIN PAYMENT ;
> >     ON CUSTOMER.CCUSTID = PAYMENT.CCUSTID ;
> >     ON CUSTOMER.CCUSTID = CCTRANS.CCUSTID ;
> >     ON CUSTOMER.CCUSTID = PACKID.CCUSTID   ;
> >  WHERE   <filter condition> ;
> >  INTO TABLE <destination>

> > Thanks

> > Ben Alicea

 
 
 

1. Query not working in SQL, used to run in Access

I have this query:
SELECT Tabelle1.Feld1 AS a1, Feld2+1 AS a2, a1+a2 AS CalcField
FROM Tabelle1;

It runs perfectly on a Access database but not on a SQL 7.0 server. I
can use the 'a1+a2 as CalcField' syntax.

Don't tell me to just use 'Feld1'+'Feld2'. I have a query where
'Feld1' is a 20 line long statement using CASE and the likes.

Can anyone give me a idea on how to convert this?

Thomas Schoch

2. Nesting BeginTrans, CommitTrans

3. Query runs in query analyser but not in SQL Agent job

4. vb6 slow to load project

5. Query using UDF joined to a sub query vs Temp Table to sub Query

6. item keys - what to use.

7. Rewrite Query using Inner Join, Left Join e.t.c

8. +o CT - Director, Systems Development, Visual Basic, C++, SPSS, SAS, Oracle, Sybase, Brio Query o+

9. Find Queries Not Using Indexes and Indexes Not Being Used

10. NOT-JOIN SQL QUERY

11. Error running SP as Job but not when run from Query Analyzer

12. Disconnected Recordset: Not editable SQL-JOIN used

13. Update query using joined query as source