psychofish Posted November 18, 2003 Share Posted November 18, 2003 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? Quote Link to comment https://forums.phpfreaks.com/topic/1388-a-complicated-select-problem/ Share on other sites More sharing options...
Barand Posted November 18, 2003 Share Posted November 18, 2003 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. Quote Link to comment https://forums.phpfreaks.com/topic/1388-a-complicated-select-problem/#findComment-4604 Share on other sites More sharing options...
psychofish Posted November 19, 2003 Author Share Posted November 19, 2003 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. Quote Link to comment https://forums.phpfreaks.com/topic/1388-a-complicated-select-problem/#findComment-4614 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.