-
What I would like to do is create an sql statement that grabs data from multiple tables, that are related by different id fields.
This is what I have tried:
"SELECT * FROM [SCHEDULE] INNER JOIN [CLIENTS] ON " & _
"[SCHEDULE].[CLIREF] = [CLIENTS].[CLIREF] AND " & _
"INNER JOIN [EMPLOYEES] ON [SCHEDULE].[EMPREF] = " & _
"[EMPLOYEES].[EMPREF]"
What I have is a schedule table that links both clients and
employees together based upon their reference fields. I need to be able to select information from the client,employee and schedule table. I think I just have the sql syntax wrong, but I can't figure it out.
Any suggestions????
-
This would be equivalent:
"Select * from Schedule s, clients c, employees e where e.cliref=c.cliref and e.empref=s.empref"
-
Thanks I already had this, but I wasn't sure if using the INNER JOINS was a better idea. My Schedule table will contain a lot of records, 100000++ and I wasn't sure if the INNER JOINS would produce faster records or not.
Any ideas on that????
Thanks in advance.
-
Technically, the sql I gave you is an inner join, just old style format.
The only thing that would make this faster was if you were able to create a view, which would make the structure, more or less, permanent. Of course, you cant create a view in Access...
-
JHausmann thanks for all your advice. The Select Statement seems to be fast enough anyway, I'm also going to set the caching size so that when I show the recordset in the grid that it only brings so many records anyway.
Thanks again.
-
just so you guys know, the "old style" joins are what is required by ADO if you are to update joined recordsets...if you use INNER JOIN in your SQL Query, ADO is unable to update your joined recordset, it will give you the nasty "insufficient base table information for updating or refreshing...."
-
If you want to make the statement as fast as possible you have to make sure that the fields that are linked between the tables are in fact primary keys.
What this does is allows you to link on these keys instead of having to "tablescan". You would find this out if you did any query planning for the SQL Statement.
If you are using something like ADO, and you know that the only values you are returning from the query are PART of the primary keys the you can use the "adKeySet" which will make the statement faster because it doesn't go to the actual tables to get the data but instead pulls them straight from the Indexes saving itself the time to have to then reference the table afterwards... but this is rare.
If you are using SQL Server 6.5 then I would go to the ISQL window and change the option to produce a query plan... then run the query and look to see if you find the word "tablescan". If you do then it could be made quicker with a few indexes :-)
