Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Question Unanswered: Query all records from one table LIKE records from another

    I have a table1 that I want to see if there are records LIKE records from table2.
    Code:
    table1	table2
    0001	0001
    0001a	0003
    0001b	0005
    0001c
    0002
    0003
    0004
    0005
    0005a
    0005b
    0006
    so the result set would return:
    0001
    0001a
    0001b
    0001c
    0003
    0005
    0005a
    0005b

    from table1

    I tried joins but the results are entries that equal the values from table2.
    What would the SQL statement be?

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Try this:

    SELECT Table1.Field1
    FROM Table1, Table2
    WHERE (((Table1.Field1) Like [Table2]![Field1] & "*"))
    ORDER BY Table1.Field1;

    TD

  3. #3
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    If you want to be specific:

    SELECT tbl1.tbl1Data
    FROM tbl1
    WHERE (((tbl1.tbl1Data) Like "0001*")) OR (((tbl1.tbl1Data) Like "0003*")) OR (((tbl1.tbl1Data) Like "0005*"));

    Buckeye's SQL will work (better) no matter what data you have and no matter how many records you have.

    If you're using QBE, put both tables into the design - they don't have to be joined. Drag the Table1.Field1 to the grid, sort it ascending, and add the following to Criteria:

    Like [Table2]![Field2] & "*"
    Last edited by wazz; 01-05-05 at 19:10.

Posting Permissions

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