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 have tried joins but that limits the results to items from table1 that = the ones from table2. What would the SQL statement be?

  2. #2
    Join Date
    Nov 2002
    Posts
    154

    Smile Try This:

    Make a new query. Put only table1 in it. Put whatever field corresponds to that series of data from the first column in the design matrix. Under criteria, type something like this: Like (In (SELECT table2ID FROM Table2)) & "*". Here table2ID represents whatever the name of your field is in table2 that has the second column of data that you gave. Good luck!

  3. #3
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Create a new select query in design mode, but don't add any tables to it. Then switch to SQL View and paste the folowing in, replacing the table names and field names with your specific table and field names if different:

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

    TD

Posting Permissions

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