Results 1 to 3 of 3

Thread: Tricky SQL - this is tough!

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2000
    Posts
    35
    Here is my situation:

    I have 2 data controls - one is for a header table and the other is for a detail table. Let's say, to simplify things, the tables look like this

    Header Table - fields
    ID
    X

    Detail - fields
    ID
    NOTE

    The recordsource for header is; SELECT * FROM HEADER WHERE X = 1 ORDER BY ID

    The recordsource for detail is; SELECT * FROM DETAIL ORDER BY ID

    Now, what I want to do, is open a third record set in my code that will use an inner join to get all the records in detail for all the records in header (where x=1). The sql without data control would look something like this (I think)

    SELECT DETAIL.ID, DETAIL.NOTE, HEADER.ID FROM DETAIL INNER JOIN HEADER ON DETAIL.ID = HEADER.ID

    I can't figure out the syntax (if even possible) on how to do this. (Because of the data controls) My closest gues is something like this.

    "SELECT " & rstDetail & ".ID, " & rstDetail & " .NOTE"
    ...etc etc.

    Does anyone have any ideas on this? if my problem is unclear please tell me.

  2. #2
    Hyperactive Member PJB's Avatar
    Join Date
    Aug 2000
    Location
    dunno at the moment
    Posts
    302
    Not sure if this is what your looking for or not...
    Code:
    Private Sub Data1_Reposition()
    Dim HEADERID As Long
    HEADERID= Data1.Recordset!ID
    Data2.recordSource = "Select * From DETAIL Where ID = " &HEADERID 
    Data2.Refresh
    End Sub
    don't know if that'll work for ya or not...
    VB6.0 SP4
    Windows 2000
    I'm thinking of a number between

  3. #3

    Thread Starter
    Member
    Join Date
    Aug 2000
    Posts
    35
    Thanks for the reply but that doesn't work. Lets say the header has 4 records, and 2 of them have Header!X = 1 and 2 have Header!X = 0. The Detail control has 8 records, to records for each Header!ID. Now my Header Data control has 2 records (the where X = 1). So what I want, is the four records that correspond with the ID's in the header. You use a join to do this. If is simple SQL, it is just messing me up because I have to use data controls. The code you show would only give me 2 detail records, the 2 that have the ID that equals HEADERID.

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