Querry problem

[drisate]

Hey guys,

I am trying to make a MySQL query but i am not sure how to build it up ... I need to count the numbers of members that are not added to the users contact list for a giving member id from the user table provided below. The "user_id" col in the contact table is the member to who the contact belongs to not the user id of the contact ... that's what complicates everything ...

 

CREATE TABLE IF NOT EXISTS `contact` (
  `id` int(9) NOT NULL auto_increment,
  `user_id` int(9) NOT NULL,
  `name` varchar(50) NOT NULL,
  `username` varchar(50) NOT NULL,
  `address` varchar(250) NOT NULL,
  `cp` varchar(50) NOT NULL,
  `tel_h` varchar(100) NOT NULL,
  `tel_w` varchar(100) NOT NULL,
  `tel_c` varchar(100) NOT NULL,
  `note` blob NOT NULL,
  `added_date` varchar(100) NOT NULL,
  `blessingstairs_ref` int(9) NOT NULL,
  `email` varchar(250) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

CREATE TABLE IF NOT EXISTS `jos_users` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `username` varchar(150) NOT NULL default '',
  `email` varchar(100) NOT NULL default '',
  `password` varchar(100) NOT NULL default '',
  `usertype` varchar(25) NOT NULL default '',
  `block` tinyint(4) NOT NULL default '0',
  `sendEmail` tinyint(4) default '0',
  `gid` tinyint(3) unsigned NOT NULL default '1',
  `registerDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `lastvisitDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `activation` varchar(100) NOT NULL default '',
  `params` text NOT NULL,
  `referenced_by` int(99) NOT NULL,
  `referenced_to` int(99) NOT NULL,
  `cron_start` varchar(40) NOT NULL,
  `cron_end` varchar(40) NOT NULL,
  `cron` varchar(40) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `usertype` (`usertype`),
  KEY `idx_name` (`name`),
  KEY `gid_block` (`gid`,`block`),
  KEY `username` (`username`),
  KEY `email` (`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=509 ;

[Zane]

I need to count the numbers of members that are not added to the users contact list for a giving member id from the user table provided below. The "user_id" col in the contact table is the member to how the contact belongs to not the user id of the contact ... that's what complicates everything ...

 

You need to count what >>--(???)-->

[drisate]

lol yeah ... umm let me try again

 

User 23 has 2 contacts

 

[email protected]

[email protected]

 

In the referal page he has 5 referals. I need to count the number of referals that are not in the contact list.

 

In this case the awnser would be 2 Then in an other page i need to fetch them then loop them

[fenway]

Sounds like a count() and a left join... is null.