Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jun 2009
    Posts
    144
    Thanks
    3
    Thanked 20 Times in 20 Posts

    A Join that exlcudes if data does exist

    I have two tables that I want to use a join to exclude data if it exists in the other table. I basically want a list of people from table B that dont exist in table A

    If A.account_id exists as B.account_id then dont want it to return in the result set. I have done it as an inclusive query using an INNER JOIN. What I need however is for it to exclude them not include them.

    My thoughts are to do a LEFT JOIN and then use PHP to exclude the result sets that have a B.account_id value. This seems awfully expensive if a query can return only the required accounts instead. (there could be thousands!)

    A push in the right direction would be greatly appreciated

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Code:
    SELECT
     b.account_id
     larry,
     curly,
     moe
    FROM 
     table b
    WHERE
     b.account_id
    NOT IN
     (SELECT a.account_id FROM table a)

  • #3
    New Coder
    Join Date
    Feb 2006
    Location
    Texas
    Posts
    80
    Thanks
    1
    Thanked 8 Times in 8 Posts
    Code:
    SELECT id,name,number FROM goodcustomers
    
    UNION
    
    SELECT id,name,number FROM badcustomers WHERE joinme NOT IN (SELECT id FROM goodcustomers)

  • #4
    Regular Coder
    Join Date
    Jun 2009
    Posts
    144
    Thanks
    3
    Thanked 20 Times in 20 Posts
    I love this place, thanks guys. Just when you feel you are starting to gain good developer skills something like this pops up!

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    > I basically want a list of people from table B that dont exist in table A

    Your idea of a left join is right (in addition to Bubikol's method). But you don't need to use PHP at all.

    Code:
    SELECT B.* 
    FROM B LEFT JOIN A ON B.accountid = A.accountid
    WHERE A.accountid IS NULL
    This will be a bit more efficient than the NOT IN scheme when you have large tables. (If MySQL were a bit smarter, the two would be identical in performance--as they are with SQL Server for example--but it's not. The LEFT JOIN has the advantage that MySQL can optimize the query better especially if both accountid fields are indexed.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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