Jump to content


Photo

a complicated SELECT problem


  • Please log in to reply
2 replies to this topic

#1 psychofish

psychofish
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 18 November 2003 - 06:21 PM

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?

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 18 November 2003 - 08:17 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 psychofish

psychofish
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 19 November 2003 - 01:48 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users