a complicated SELECT problem

[psychofish]

Hi,

 

I need some help with a MySQL command.

 

I have three tables for an address book: contact, which has the list of people; address, which has a list of addresses; and groups, which is what group the person belongs in. A person may belong in more than one group or none at all. I have the following code to retrieve the contact and primary address if given a group ID number ($groupID).

 

SELECT contact.id, CONCAT(lastname,\', \',firstname) AS fullname, lastname, firstname, line1, line2, city, state, zip, phone1, phone2, country FROM address_contact as contact, address_groups as groups LEFT JOIN address_address as address ON contact.id=address.id AND contact.primaryAddType=address.type WHERE contact.id=groups.id AND groups.groupid=$groupID ORDER BY fullname;

 

What I can\'t figure out is how to retrieve a list of contacts (with their primary addresses) that belong in NO group. Does anyone have any suggestions?

[Barand]

You say 3 tables. person, address and group. If a person can belong to many (or 0) groups and a group has many persons, then there must be 4th table to hold person/group membership.

 

If you have person LEFT JOIN 4th_table you\'ll find those with no group where 4th_table.personid is NULL.

[psychofish]

SELECT contact.id, CONCAT(lastname,\', \',firstname) AS fullname, lastname, firstname, line1, line2, city, state, zip, phone1, phone2, country FROM address_contact as contact LEFT JOIN address_address as address ON contact.id=address.id AND contact.primaryAddType=address.type LEFT JOIN address_groups AS groups ON groups.id=contact.id WHERE groups.id IS NULL ORDER BY fullname

 

This seems to be working... thanks.

 

The groups table in my code IS the 4th table. The one that contains the list of groups (ie. the 3rd table) isn\'t used in the select query.