Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Posts
    7

    Question Unanswered: complicated selection from two tables

    Here's a simplified version of my problem.

    I have this kind of relationship in two tables:
    table1.object table2.object
    3150#* 3150
    6988#* 6988

    I'm trying to select ALL the objects from table2 where the object code is equal to that of table1 but without the hash and the star in the end.
    So something like

    SELECT ALL table2.object FROM table2, table1
    WHERE
    (table2.object = table1.object <plus #* in the end>);

    I'm trying to do this in the SQL view of the Query (so I guess I shouldn't use something like a VIEW or a temporary table). And again, I'm not searching only for one certain object (so I can't use LIKE "objectname#%").

    The database from which I'm retrieving data, is an ORACLE one and I'm not able to make a better and easier relationship between the two fields.

    Please help!!

    Maarit

  2. #2
    Join Date
    Jun 2002
    Location
    Central New Jersey
    Posts
    69
    If the field in table 1 is a constant length you can select only the correct # of characters to make the fields equal using the LEFT() function.

    Using your example, LEFT(table1.object,4) would equal table2.object.

    Here's the SQL code you can paste in your query...


    SELECT Table2.object
    FROM Table1, Table2
    WHERE (((Table2.object)=Left([table1].[object],4)));


    Hope this helps.

    Jim
    "There's never enough time to do it right, but there's always enough time to do it over!"

Posting Permissions

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