Learning SQL
I work in a large company in the automotive industry. We use the AS400 system corporately. I am just learning the query system. I have used a little of the iSeries and found that to be somewhat burdensome. My co-worker introduced me to the SQL language. What I am trying to do is using MS Access and a pass-through query to bring in more then one AS400 file to a single query. I have set up the passthrough query and developed the Select, Where, From statements. I have trying to linking and either my statements are wrong or it is my inexperience. Is it possible to bring more then one table in to a single query? The way I have been avoiding it is by developing single queries of the single tables and then allow MS Access to do the query of a query. Is there any info you can give me?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 27, 2009 12:39 PM
UPDATED:
July 28, 2009 8:33 PM
Answer Wiki:
Yes, multiple tables can be accessed in one SQL statement. That is one of the beautiful things of SQL. Your approach of using separate queries is way off base. Sure, it works, but what is the point.
Here is an example of accessing multiple tables in one statement (and there are MANY ways to do this):
(this is a simple join of two tables)
SELECT a.col1, b.col2
FROM tablea a, tableb b
WHERE a.col3 = b.col4
AND ...
ORDER BY ...
You need an introduction to SQL before you go any further. SQL, like so much in this world, is easy to misuse. Learn to put the work into the SQL. Make the SQL do the bulk of your processing.
Good luck.
To see all answers submitted to the Answer Wiki: View Answer History.
One thing to note when using pass-through queries is that the dialect of SQL must exactly match the one in use by the target database. Access SQL may not match the target database SQL. Look up the language reference for AS400 SQL to be sure.
There are differences in more than one subject area, such as wildcard character, string delimiters, and date type representations.
If you are using ODBC connections, some of the datatype translations are automated, but be sure to test the result, as the format returned may not exactly match what you expected.
If you are combining data in Access with data from the AS400, you will need a second query to join it with the AS400 query result.