Intersect in MYSQL

Databases
1

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.

2 
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 
   [COLOR="#0000FF"]AND membership_payments.user_id IS NULL[/COLOR]
ORDER 
    BY members.last_name ASC
3

Thanks it works, So I needed left outer join.

4

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”

:slight_smile:

5

yeah :slight_smile:

Interset will be INNER JOIN.