Results 1 to 4 of 4
  1. #1
    Tyork is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    35

    LEFT JOIN leaving out records in left table

    I’m trying to execute the following query:




    Code:
    SELECT dbo_master.Number, dbo_master.Original, dbo_master.Paid, dbo_master.Score, dbo_MiscExtra.TheData as ChargeOffCode
    FROM dbo_master LEFT JOIN dbo_MiscExtra 
    ON dbo_master.Number = dbo_miscextra.number
    WHERE dbo_Master.customer = 'ADS1S' AND dbo_miscextra.Title = 'Charge-off Reason Code: '
    It executes however my problem is as follows.

    I want ALL of the records from dbo_master to show up in my query result where customer = 'ADS1S' regardless of whether or not there is a matching record in dbo_miscextra.
    This query

    Code:
    SELECT count(*)
    FROM  dbo_master
    WHERE dbo_Master.customer = 'ADS1S'
    Results in 245,617 records while this query


    Code:
    SELECT count(*)
    FROM  dbo_master LEFT JOIN dbo_MiscExtra 
    ON dbo_master.Number = dbo_miscextra.number
    WHERE dbo_Master.customer = 'ADS1S' AND dbo_miscextra.Title = 'Charge-off Reason Code:   '
    Results in 94,154 records

    I want my final result to include 245,617 records and 94,154 of them include a result for the ChargeOffCode. I'm sure it has to do with adding the additional [AND] statement in the WHERE clause but I can't think of another way to execute the query. Maybe I have to first create a tempory table and do the join on that, but I'm thinking there is an easier way.

    Any help would be appreciated.

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    your AND in the WHERE clause is limiting your results, I believe. Try
    AND (dbo_miscextra.Title = 'Charge-off Reason Code: ' OR Is Null)

    That's assuming that field is always filled in. It would only be Null if it doesnt have a match in the Master table.

  3. #3
    Tyork is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    35
    This works:

    Code:
     
    SELECT dbo_master.Number, dbo_master.Original, dbo_master.Paid, dbo_master.Score, dbo_miscextra.TheData as ChargeOffCode
    FROM  dbo_master LEFT JOIN dbo_MiscExtra 
    ON dbo_master.Number = dbo_miscextra.number 
    where (1=1)
    and dbo_miscextra.Title = 'Charge-off Reason Code:   '
    and dbo_Master.customer = 'ADS1S'
    UNION
    SELECT dbo_master.Number, dbo_master.Original, dbo_master.Paid, dbo_master.Score, dbo_miscextra.TheData as ChargeOffCode
    FROM  dbo_master LEFT JOIN dbo_MiscExtra 
    ON dbo_master.Number = dbo_miscextra.number 
    where (1=1)
    and dbo_miscextra.Title is null
    and dbo_Master.customer = 'ADS1S'

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Glad you got it working.

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

Similar Threads

  1. Left join problem
    By e.badin in forum Queries
    Replies: 5
    Last Post: 01-17-2011, 08:03 AM
  2. LEFT JOIN Breaks with Where Clause
    By jascraig in forum Queries
    Replies: 3
    Last Post: 08-31-2010, 08:56 AM
  3. Problem with Left Join & VB6
    By msrdjan1 in forum Queries
    Replies: 0
    Last Post: 03-30-2010, 01:48 AM
  4. Error using left join accessing 2 active connections
    By peterg2000 in forum Programming
    Replies: 0
    Last Post: 10-05-2009, 05:04 PM
  5. Update with LEFT JOIN
    By mcarthey in forum Access
    Replies: 1
    Last Post: 08-27-2008, 10:49 AM

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