sql question

[gentforreal]

I have three tables Zenter which contains only 2 zipcodes, Zmake which contains only 3 makes of autos and Automaster with contains 1,900,000 records which includes a persons zip and the make they own.


Shouldn't the following display all the zips and makes associated with the first two tables them from the automaster? What I want to get are the hundreds of records I know are in the automaster.

All I have returned is the first zip in zenter and the first make in zmake.


select * from automaster;
where zmake.make = automaster.make;
and zenter.czip = automaster.zip

 

[zhavic]

Try this:

select * from automaster;
inner join zmake on zmake.make = automaster.make;
inner join zenter on zenter.czip = automaster.zip

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[KDavie]

You need to INNER JOIN the zip and make fields instead of using the 'WITH' clause. For example:

SELECT AutoMaster.* ;
FROM (AutoMaster INNER JOIN Zenter ON AutoMaster.zip = ; Zenter.zip) INNER JOIN Zmake ON AutoMaster.make = Zmake.make


I would recommend using the Query designer to write your SQL for you and looking at the SQL statements until you get more comfortable with the code.

-Kevin

 

[darrellblackhawk]

Select * from automaster a ;
  where a.make in (select make from zmake b) .and. ;
  a.zip in (select czip from zenter c) order by Whatever_Field_You_Like ;
  into cursor What_Ever_Cursor_You_Like

Darrell

 

[jimoo]

Although SELECT statements are convenient to run, they are slower with when the table has a large amount of records and you only want to return a few.


* this type of logic generally returns the data much faster. It assumes and index of id on both tables.
Select employee
SCAN
Select sales
Seek employee.id
IF FOUND()
DO WHILE employee.id = sales.id and NOT EOF()
Append record, etc.
SELECT sales
SKIP
ENDDO
ENDIF
Select employee
ENDSCAN



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[gentforreal]

Thanks.. the inner join statement escaped me cause of a brain fart I suppose.

I am rethinking this thanks to Jim... it is a very large database... the final compilation will be over 4 million records and just dealing with the shear size alone is a challenge in itself. I'm using these statements in forms so anyone can query the database with out affecting (infecting? LOL) the original database. I will look at the scan option this week. Its an interesting idea Jim.