Results 1 to 5 of 5

Thread: JOIN question

  1. #1
    Join Date
    Mar 2004
    Posts
    52

    Unanswered: JOIN question

    I want to link two tables, each entry in the first table has to be matched with the FIRST matching record of the 2nd tabel. How do I do this?


    tblA
    ----
    tblA_id:1
    tblA_id:2
    tblA_id:3

    tblB
    ----
    tblB_id:1 tblA_id:1
    tblB_id:2 tblA_id:1
    tblB_id:3 tblA_id:2
    tblB_id:4 tblA_id:3

    Wanted result:
    -------------
    tblA_ID: 1 tblB_id:1
    tblA_ID: 2 tblB_id:3
    tblA_ID: 3 tblB_id:4

    What I get with a INNER or LEFT JOIN:
    -------------------------------------
    tblB_id:1 tblA_id:1
    tblB_id:2 tblA_id:1
    tblB_id:3 tblA_id:2
    tblB_id:4 tblA_id:3

  2. #2
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    For that you'll need to create a union query as

    SELECT tblA_id FROM TblA
    UNION
    SELECT TblB_id FROM TblB

  3. #3
    Join Date
    Mar 2004
    Posts
    52

    Question

    I tried this, but it didn't do the job Instead it gave (all in one colummn)
    tblA_id:1
    tblA_id:2
    tblA_id:3
    tblB_id:1
    tblB_id:2
    tblB_id:3
    tblB_id:4

    What I want is per tblA_ID the first tblB record that matches with the A_ID
    Last edited by ptrapp; 08-23-04 at 06:35.

  4. #4
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    So In that case use the following (assuming both tables are linked via relationships on their primay and foriegn keys)

    SELECT TblB.AID, First(TblB.BID) AS FirstOfBID
    FROM TblA, TblB
    GROUP BY TblB.AID;


    - Saqib
    Attached Files Attached Files

  5. #5
    Join Date
    Mar 2004
    Posts
    52
    Thanks for your help, it did it.

    Kind regards,

    Philip

Posting Permissions

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