Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    7

    Question Unanswered: INNER JOIN and DISTINCT Clause

    Im new to sql and access so bare with me. I'm trying to inner join two tables and get rid of duplicate vin #'s in one query. Can this be done?

    Here is some data I have. I know it's not going to work like this. But can anyone please help???

    SELECT [ihDinshdr(Old-1-TMIN031920091350)].ih_ibid, [ihDinshdr(Old-1-TMIN031920091350)].ih_vin, [ihDinshdr(Old-1-TMIN031920091350)].ih_eqpinit, [ihDinshdr(Old-1-TMIN031920091350)].ih_eqpno, [ihDinshdr(Old-1-TMIN031920091350)].ih_carrier, [ihDinshdr(Old-1-TMIN031920091350)].ih_iaid, [ihDinshdr(Old-1-TMIN031920091350)].ih_idtm, [ihDinshdr(Old-1-TMIN031920091350)].ih_purpose, [ihDinshdr(Old-1-TMIN031920091350)].ih_idate, [ihDinshdr(Old-1-TMIN031920091350)].ih_location, [ihDinshdr(Old-1-TMIN031920091350)].ih_rampid, [ieDinsdtl(Old-1-TMIN031920091350)].ie_arid, [ieDinsdtl(Old-1-TMIN031920091350)].ie_ardesc, [ieDinsdtl(Old-1-TMIN031920091350)].ie_tyid, [ieDinsdtl(Old-1-TMIN031920091350)].ie_tydesc, [ieDinsdtl(Old-1-TMIN031920091350)].ie_svid, [ieDinsdtl(Old-1-TMIN031920091350)].ie_svdesc
    FROM [ihDinshdr(Old-1-TMIN031920091350)] INNER JOIN [ieDinsdtl(Old-1-TMIN031920091350)] ON ([ihDinshdr(Old-1-TMIN031920091350)].ih_ihid=[ieDinsdtl(Old-1-TMIN031920091350)].ie_ihid) AND ([ihDinshdr(Old-1-TMIN031920091350)].ih_ibid=[ieDinsdtl(Old-1-TMIN031920091350)].ie_ibid);

    SELECT DISTINCT ih_vin
    FROM [ihDinshdr(Old-1-TMIN031920091350)];

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First create your Inner Join query (say [ihDinshdr-Inner-ieDinsdtl]), then base your Distinct query on the first one:

    Code:
    SELECT DISTINCT ih_vin FROM [ihDinshdr-Inner-ieDinsdtl];
    You can also do it in one step:

    Code:
    SELECT DISTINCT ih_vin FROM (SELECT .... INNER JOIN ...)
    but it's not easily readable and harder to debug in case of problem.

    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    7

    Thank you....

    I appreciate the help I will definitely try that out.
    thanks again.

  4. #4
    Join Date
    Mar 2009
    Posts
    7

    example (Access)

    Will your solution work with access? That's what I'm using right now.
    thanks again.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It works here anyway, and I'm using Access 2003 SP3.

    Have a nice day!

Posting Permissions

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