-
I am attempting to query 3 tables. The topmost table is what I call the Master Table. The other two are the Detail tables. I need to filter the query based on critera from either or all tables to create one recordset.
For instance I want all the records from the Master table that have a value = XX in either of the detail tables AND a value = OO in the Header table.
A secondary question would be...I spend at least 15 hours a week writing complex sql statements. Can anyone recommend a source for advanced sql training, so I can stop wasting so much time on searching help files and trial and error.
Thank you in advance.
Carroll
-
Tray this:
SELECT * FROM MainTable WHERE field1 IN(SELECT * FROM table2 WHERE fieldX IN(SELECT * FROM table3 WHERE fieldY='xyz'))
Good look!
-
here is a good reference site: http://w3.one.net/~jhoffman/sqltut.htm
Delete from Hair where Hair.Count > 0
-
Thank you for the suggestions. I have tried
SQL = "SELECT [Aircraft Type],[Reg No], " _
& "Max([Total Time]) - Min([Total Time]) AS TTDiff " _
& "FROM WorkOrders WHERE [W/O_Number] IN (SELECT [W/O_Number] FROM WoParts WHERE [W/O_Number] IN(SELECT [W/O_Number] FROM WoLabor WHERE ataCode = ""51-01""))" _
& "Group By [Aircraft Type], [Reg No] ORDER BY WorkOrders.[Aircraft Type]"
The detail tables 'WoParts' and 'WoLabor' both have a field ataCode. I want to return the records from the Workorder table that have an associated record in either of the detail tables where at least one of the records match the ataCode.
When I run the above code VB locks up and stops responding. *ARGH*
The WoParts table has 3312 records, the WoLabor table has 5605 records and the Master table 'Workorders' only has 156 records.
This doesn't seem like an unreasonable request, that is to return the master record when detail records exist filtered by criteria. Actually it works if I query only one of the detail tables. I can not get it to work if I query both detail tables.
Any suggestions are much appreciated!
[This message has been edited by Carroll (edited 01-08-2000).]
