Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Query shows result even condition dont match

    Hi:
    This query works fine when join and where clause match. Is there any way if even one of them not match it will still shows the data

    SELECT LastValidTech.Account1, LastValidTech.LstVldTech, tech_id.TECHCONT, tech_id.CORP, LastValidTech.ReportID
    FROM LastValidTech LEFT JOIN tech_id ON LastValidTech.LstVldTech = tech_id.TECH
    WHERE (((tech_id.CORP)=Val(Left([LastValidTech].[Account1],5))))
    GROUP BY LastValidTech.Account1, LastValidTech.LstVldTech, tech_id.TECHCONT, tech_id.CORP, LastValidTech.ReportID;

    Thanks.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Since you're limiting records in your where statement based on it being in both
    tables, it will always return just matching records.

    See if this gives you what you need:

    Code:
    SELECT LastValidTech.Account1, LastValidTech.LstVldTech, 
    IIF(ISNULL(tech_id.TECHCONT)=-1,"",tech_id.TECHCONT), 
    IIF(ISNULL(tech_id.CORP)=-1,"",tech_id.CORP), 
    LastValidTech.ReportID
    FROM LastValidTech LEFT JOIN tech_id ON LastValidTech.LstVldTech = tech_id.TECH
    WHERE (((tech_id.CORP)=Val(Left([LastValidTech].[Account1],5))))
    OR TECH_ID.TECH IS NULL
    GROUP BY LastValidTech.Account1, LastValidTech.LstVldTech, IIF(ISNULL(tech_id.TECHCONT)=-1,"",tech_id.TECHCONT), 
    IIF(ISNULL(tech_id.CORP)=-1,"",tech_id.CORP)
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2006
    Posts
    157
    Its doing the same thing. When both condition match it shows data otherwise empty record. I try in this way too but it also dont work.

    SELECT LastValidTech.Account1,LastValidTech.LstVldTech,te ch_id.TECHCONT,tech_id.CORP, LastValidTech.ReportID FROM LastValidTech LEFT JOIN tech_id ON LastValidTech.LstVldTech = tech_id.TECH WHERE (((tech_id.CORP)=Val(Left([LastValidTech].[Account1],5)))) GROUP BY LastValidTech.Account1, LastValidTech.LstVldTech, tech_id.TECHCONT, tech_id.CORP, LastValidTech.ReportID;

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    When I try this on my own data, it works fine, so I'm not sure what to tell you.

    Just for grins, try it this way...

    SELECT LastValidTech.Account1, LastValidTech.LstVldTech,
    IIF(ISNULL(tech_id.TECHCONT)=-1,"",tech_id.TECHCONT),
    IIF(ISNULL(tech_id.CORP)=-1,"",tech_id.CORP),
    LastValidTech.ReportID
    FROM LastValidTech LEFT JOIN tech_id ON LastValidTech.LstVldTech = tech_id.TECH
    WHERE TECH_ID.TECH IS NULL
    GROUP BY LastValidTech.Account1, LastValidTech.LstVldTech, IIF(ISNULL(tech_id.TECHCONT)=-1,"",tech_id.TECHCONT),
    IIF(ISNULL(tech_id.CORP)=-1,"",tech_id.CORP)

    You should get ONLY the unmatched records now
    Inspiration Through Fermentation

Posting Permissions

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