Results 1 to 4 of 4

Thread: Pulling my hair out with sql queries

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2000
    Location
    Colorado, USA
    Posts
    18

    Post

    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



  2. #2
    Hyperactive Member
    Join Date
    May 1999
    Location
    Reynosa, Mexico
    Posts
    274

    Post

    Tray this:

    SELECT * FROM MainTable WHERE field1 IN(SELECT * FROM table2 WHERE fieldX IN(SELECT * FROM table3 WHERE fieldY='xyz'))

    Good look!

  3. #3
    Guru Clunietp's Avatar
    Join Date
    Oct 1999
    Location
    USA
    Posts
    1,844

    Post

    here is a good reference site: http://w3.one.net/~jhoffman/sqltut.htm

    Delete from Hair where Hair.Count > 0

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Jan 2000
    Location
    Colorado, USA
    Posts
    18

    Post

    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).]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width