Results 1 to 3 of 3
  1. #1
    Bobby100 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    13

    INNER JOIN using WHERE clause

    Hi,

    I have 2 tables that contain information about company assets. Essentially these two tables are describing the same assets but they have their own conventions so they may have slightly different asset names.

    Additionally they both have information about their location stored in terms of distance along a route name. There are several different possible routes.

    Firstly I want to join the tables by route name.

    Then I want to join by PLANT_NO. The PLANT_NO are different between these tables but generally they will contain a portion of the name from one table within the other table.

    I tried this to just do the second part for now (will try the join by route name after) but no records were returned..

    SELECT *
    FROM TABLE_A
    INNER JOIN TABLE_B
    ON TABLE_A.PLANT_NO=TABLE_B.PLANT_NO
    WHERE TABLE_.PLANT_NO LIKE '*[TABLE_B.PLANT_NO]*'

    Any help would be greatly apprecaited.



    Thanks,

    Rob.

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The inner join

    INNER JOIN TABLE_B ON TABLE_A.PLANT_NO=TABLE_B.PLANT_NO

    will already exclude any records where Plant_No is not the same is both tables, so the WHERE clause is redundant.

    But the WHERE clause you have is causing the problem of no records. You have [TABLE_B.PLANT_NO] inside the quotation marks, so it takes that as a literal string and does not substitute the value.

    Where are you using that SQL?

  3. #3
    Bobby100 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    13
    Sorry for the late reply.. I have just returned from holiday.

    Thanks for the response.

    I have since been trying to join the two table by plant_no using a where clause as below..

    SELECT * FROM TABLE1, TABLE2
    WHERE ((('*TABLE1.PLANT_NO') LIKE Left([TABLE2].[PLANT_NO],4))) ;

    But I'm not sure this is the correct syntax.

    Basically one table has a column with a 3 or 4 digit plant number (PLANT_NO) and the other table has a column with a 12 character alphanumeric plant number (PLANT_NO) with the first 4 characters that would match to the plant number from the first table.

    I have used the left() function to clip the text of 12 character identifier to four characters and then joined where the PLANT_NO columns of each table are equal.

    But it is now where I am trying to join the tables where the plant numbers are less than 4 characters, (always 3 characters actually) so one PLANT_NO in Table1 will be '825' for instance and the corresponding PLANT_NO in Table2 will be 0825 00MA STST (which will be clipped to '0825' with the left() function).

    So trying to match '825' with '0825'

    so essentially something along the lines of WHERE '*825' Like '0825' but replacing specific values with column values.

    So as stated earlier this is what I tried but didnt seem to work...

    SELECT * FROM TABLE1, TABLE2
    WHERE ((('*TABLE1.PLANT_NO') LIKE Left([TABLE2].[PLANT_NO],4))) ;

    Any help greatly appreciated.

    Thanks,

    Rob.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. sql subquery alias join from clause error
    By rwhite7 in forum Access
    Replies: 1
    Last Post: 12-16-2014, 02:06 PM
  2. Replies: 6
    Last Post: 11-19-2013, 01:38 AM
  3. Inner Join works but not Left Join
    By kwooten in forum Queries
    Replies: 1
    Last Post: 12-11-2012, 05:09 PM
  4. INNER JOIN and WHERE clause problems
    By kman42 in forum Queries
    Replies: 2
    Last Post: 08-04-2011, 11:29 AM
  5. LEFT JOIN Breaks with Where Clause
    By jascraig in forum Queries
    Replies: 3
    Last Post: 08-31-2010, 08:56 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums