join - where

[gentforreal]

I know it must be a simple answer

select * from maintable;
where maintable.dr<>dtoc('01/06/54');
inner join zmake2 on zmake2.make = maintable.make;
inner join zenter2 on zenter2.czip = maintable.zip;


I am not putting the where clause in the right place. This is doable, right? I've tried several varitions of exactly where to put the 'where' clause to no avail.

 

[EzLogic]

put it after the last inner join

Ali Koumaiha
Wireless Toyz
Farmington Hills, Michigan

 

[craigsboyd]

Select * from maintable, zmake2, zenter2 ;
where maintable.dr <> dtoc('01/06/54') ;
and zmake2.make = maintable.make ;
and zenter2.czip = maintable.zip

...at least until you get the hang of nested or non-nested joins the above statement is equivalent to what I presume you are trying to do. The thing to remember is that joins are part of the FROM clause not part of the WHERE clause. You may also go make some queries using the wizard in VFP and look at the SQL that is generated. It's not the best SQL sometimes, but is a good starting point.

[sub]craig1442@mchsi.com[/sub][sup]

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

[/sup]

 

[baltman]

I see you have no spaces before your semi-colons. Don't know if that's the issue, but it won't help... you need the space or the line will compile as demonstated below:

maintable;
where

as

maintablewhere

 

[BPeisch]

Craig,

Just wondering, why did you suggest that gentforreal remove the inner joins? I prefer doing joins with the "inner join" syntax rather than in the where clause, because it clearly separates your joins from your filters.


-BP (Barbara Peisch)

http://www.peisch.com

 

[craigsboyd]

BPeisch,

I was suggesting it in an attempt to get gentforreal's head around what it was that he was trying to do. Gentforreal was placing the inner joins in as part of the where clause and I thought that my example would show the difference between what it would look like if it was part of the WHERE clause as opposed to the FROM clause. It was not meant to imply anything regarding my personal preferences regarding inner join notation, I just saw the opportunity given the equi-joins to flip it a bit and allow gentforreal to see it from a different angle hoping that perhaps a light would go on.

In retrospect I probably should have qualified my post a little better. I didn't mean to give the impression that I was opposed to expressing inner joins in the from clause. In fact, if asked i would argue that expressing an inner join in the where clause is antiquated since that style of notation was part of the SQL1 (SQL-89) standard, where as using Inner Join in the from clause was provided for with the SQL2 standard and the previous way of expressing it was pretty much included in SQL2 for backward compatibility reasons.

[sub]craig1442@mchsi.com[/sub][sup]

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

[/sup]