please help me its driving me nuts

[minckle]

Please can some 1 help me

I have 3 tables
1 with contacts in
1 with groups in
and 1 to link the 2 together

each contact and group have id's which are used in the link table

i need 2 sql queries
1 saying which contacts are in each group (i think ive cracked this)
and 1 saying which contacts are not in each group(which i need help)

Some 1 please help its driving me nuts

[nicc777]

First of all, you don't really need 3 tables. I have created a little test:

DROP TABLE IF EXISTS `t_contacts`;
CREATE TABLE `t_contacts` (
`id` int(10) unsigned NOT NULL auto_increment,
`groupid` int(11) unsigned NOT NULL default '0',
`name` varchar(128) NOT NULL default '',
`email` varchar(128) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

INSERT INTO `t_contacts` (`id`, `groupid`, `name`, `email`) VALUES (1, 1, 'name1', 'email1@aaa.com'),
(2, 1, 'name2', 'name2@bbb.com'),
(3, 3, 'name3', 'name3@ccc.com'),
(4, 0, 'name4', 'name4@ddd.com');

DROP TABLE IF EXISTS `t_groups`;
CREATE TABLE `t_groups` (
`groupid` int(10) unsigned NOT NULL auto_increment,
`name` varchar(128) NOT NULL default '',
PRIMARY KEY (`groupid`)
) TYPE=MyISAM;

INSERT INTO `t_groups` (`groupid`, `name`) VALUES (1, 'Group Nr 1'),
(2, 'Group Nr 2'),
(3, 'Group Nr 3');

As you can see, there are 3 groups and 4 users. One group isn't used, and one user has to group assigned.

Here is some interesting queries you can try:

mysql> SELECT a.name, b.name FROM t_contacts AS a, t_groups AS b WHERE a.groupid = b.groupid;
+-------+------------+
| name | name |
+-------+------------+
| name1 | Group Nr 1 |
| name2 | Group Nr 1 |
| name3 | Group Nr 3 |
+-------+------------+
3 rows in set (0.00 sec)

The above query satisfied your first problem. The second problem concerns people not assigned to a group:

mysql> SELECT * FROM t_contacts WHERE groupid = '' OR groupid = NULL;
+----+---------+-------+---------------+
| id | groupid | name | email |
+----+---------+-------+---------------+
| 4 | 0 | name4 | name4@ddd.com |
+----+---------+-------+---------------+
1 row in set (0.00 sec)

Further, to select stats from a specific group:

mysql> SELECT a.name, a.email FROM t_contacts AS a, t_groups AS b WHERE a.groupid = b.groupid AND b.name = 'Group Nr 1';
+-------+----------------+
| name | email |
+-------+----------------+
| name1 | email1@aaa.com |
| name2 | name2@bbb.com |
+-------+----------------+
2 rows in set (0.00 sec)

This last query could also be a simple:

SELECT name,email FROM t_contacts WHERE groupid = '1';

I hope this somewhat addresses your needs?

Cheers

[greenman]

This query has been double-posted, so it's also answered at great length in the Ask an Expert forum but the answer here is much better!