Intersect in MYSQL

[hiddenpearls]

hi,

I'm trying to remove second table entries that matches in first table but confused
here is my 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.

[r937]

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

[hiddenpearls]

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.

[r937]

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"

[hiddenpearls]

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

yeah

Interset will be INNER JOIN.