Hi there

This should probably be very simple but I just can figure it out

I have 2 tables :

t1 : 'tasks' table with a taskid, taskdescription ...
t2 : 'company2task' with a companyid, taskid ...

what I need to do is get all tasks from t1 which don't have a row with companyid "x" in t2 ... basically it's all the tasks that the company hasn't done yet

So I'm starting with

"SELECT t1.* FROM tasks AS t1 LEFT JOIN company2task AS t2 ON t1.taskid = t2.taskid WHERE ..."

what would be the way to say "WHERE there ISN'T a row in t2 with companyid=x" ????

thanks

    If I understand correctly:

    ... WHERE t2.companyid != x

      Hi

      Thanks for your reply

      I'd tried that one, like this :

      SELECT * FROM tasks AS t1 LEFT JOIN company2task AS t2 ON t1.taskid = t2.taskid WHERE t2.companyid != 10 LIMIT 20

      but it doesn't give me any results, whereas it should return the 9 tasks that don't have a row in the 'company2task' table (the company has already done 1 task)

      if I do :

      SELECT * FROM tasks LIMIT 20

      I get all 10 existing tasks

        Is this any better?

        SELECT * FROM tasks AS t1 
LEFT JOIN company2task AS t2 ON t1.taskid = t2.taskid AND t2.companyid != 10 
LIMIT 20

          That query returns all 10 tasks but with NULL in the 'companyid' col for the task that HAS been done and IS in the 'company2task' table !!

          here's the structure of the 2 tables if that's any help

          CREATE TABLE IF NOT EXISTS `tasks` (
  `taskid` int(11) NOT NULL AUTO_INCREMENT,
  `taskcatid` int(11) NOT NULL,
  `pagetype` int(11) NOT NULL,
  `tasktype` int(11) NOT NULL,
  `priority` tinyint(1) NOT NULL,
  `taskrank` int(11) NOT NULL,
  `tasklevel` tinyint(4) NOT NULL,
  `taskname_en` tinytext NOT NULL,
  `taskname_fr` tinytext NOT NULL,
  `taskmainpage_en` tinytext NOT NULL,
  `taskmainpage_fr` tinytext NOT NULL,
  `taskrepeat` int(4) DEFAULT '0' COMMENT 'num days',
  `taskonline` tinyint(1) NOT NULL DEFAULT '0',
  `taskeasy` tinyint(4) NOT NULL,
  `tasktime` int(11) NOT NULL,
  `taskh1_en` tinytext NOT NULL,
  `taskh1_fr` tinytext NOT NULL,
  `taskslug_en` tinytext NOT NULL,
  `taskslug_fr` tinytext NOT NULL,
  `taskmetatitle_en` tinytext NOT NULL,
  `taskmetatitle_fr` tinytext NOT NULL,
  `taskmetadesc_en` text NOT NULL,
  `taskmetadesc_fr` text NOT NULL,
  `tasktext1_en` text NOT NULL,
  `tasktext1_fr` text NOT NULL,
  `tasktext2_en` text NOT NULL,
  `tasktext2_fr` text NOT NULL,
  `tasktext3_en` text NOT NULL,
  `tasktext3_fr` text NOT NULL,
  `taskvideo_en` tinytext NOT NULL,
  `taskvideo_fr` tinytext NOT NULL,
  `taskvideopic_en` tinytext NOT NULL,
  `taskvideopic_fr` tinytext NOT NULL,
  PRIMARY KEY (`taskid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;



CREATE TABLE IF NOT EXISTS `company2task` (
  `companyid` int(11) NOT NULL,
  `taskid` int(11) NOT NULL,
  `taskdonedate` date NOT NULL,
  `postponetodate` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `company2task` (`companyid`, `taskid`, `taskdonedate`, `postponetodate`) VALUES
(10, 1, '2015-02-02', '0000-00-00');

          so the company with the 'companyid' of 10 has done the task with the 'taskid' of 1 so I need to return all the other 9 tasks

            or maybe I need to RIGHT JOIN or something - I'm never really sure when to use which type of JOIN

              Yowza !!

              I think I've found it

              Select * FROM tasks AS t1 WHERE NOT EXISTS (Select * FROM company2task AS t2 WHERE t1.taskid=t2.taskid and t2.companyid = 10 )

              I need to test it a bit more but it's looking good

              thanks for your help !

                Write a Reply...