SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Intersect in MYSQL

    hi,

    I'm trying to remove second table entries that matches in first table but confused
    here is my SQL
    Code SQL:
    SELECT
       members.id AS ID
     , first_name
     , last_name
     , street_address
     , zip
     , state
     , country
     , birth_date
     , email
     , phone
     , occupation
     , membership_accepted
     , full_membership
    FROM
       members
       LEFT JOIN
       membership_payments
       ON
          members.id=membership_payments.user_id
    WHERE
       (members.membership_accepted=1 AND members.full_membership=1)
    ORDER BY
       members.last_name ASC

    It shows me members table with all of its entries. If i use INNER JOIN then it shows just the matches entries from both table. but don't know how I can get my required results.
    Last edited by ScallioXTX; Nov 5, 2011 at 04:33. Reason: formatted query

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT members.id AS ID
         , members.first_name
         , members.last_name
         , members.street_address
         , members.zip
         , members.state
         , members.country
         , members.birth_date
         , members.email
         , members.phone
         , members.occupation
         , members.membership_accepted
         , members.full_membership 
      FROM members 
    LEFT OUTER
      JOIN membership_payments 
        ON membership_payments.user_id = members.id
     WHERE members.membership_accepted = 1 
       AND members.full_membership = 1 
       AND membership_payments.user_id IS NULL
    ORDER 
        BY members.last_name ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT members.id AS ID
         , members.first_name
         , members.last_name
         , members.street_address
         , members.zip
         , members.state
         , members.country
         , members.birth_date
         , members.email
         , members.phone
         , members.occupation
         , members.membership_accepted
         , members.full_membership 
      FROM members 
    LEFT OUTER
      JOIN membership_payments 
        ON membership_payments.user_id = members.id
     WHERE members.membership_accepted = 1 
       AND members.full_membership = 1 
       AND membership_payments.user_id IS NULL
    ORDER 
        BY members.last_name ASC
    Thanks it works, So I needed left outer join.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hiddenpearls View Post
    So I needed left outer join.
    you already had left outer join

    what you needed was the IS NULL check, to isolate those members who don't have a payment

    by the way, this is called "except" not "intersect"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post

    by the way, this is called "except" not "intersect"

    yeah

    Interset will be INNER JOIN.


Tags for this Thread

Bookmarks

Posting Permissions

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