SELECT query problem...

[carrot]

I'm creating a network documentation thingy, and in the form where I put information about computers, I've got two troublesome fields: one that contains the software already installed on the computer, the other the software available for installation. If a particular piece of software is installed on the current computer, it shouldn't show up in the list of available software.

I've got these two tables:

CREATE TABLE `tblSoftware` (
  `softwareID` int(10) unsigned NOT NULL auto_increment,
  `programName` varchar(45) NOT NULL default '',
  `purchaseDate` date default '0000-00-00',
  `owner` varchar(45) default NULL,
  `licenseNumber` varchar(100) default '',
  `totalLicenses` int(10) unsigned NOT NULL default '0',
  `usedLicenses` int(10) unsigned NOT NULL default '0',
  `notes` text,
  `createdBy` varchar(45) default NULL,
  `createDate` date default '0000-00-00',
  `lastEditBy` varchar(45) default NULL,
  `lastEditDate` date default '0000-00-00',
  PRIMARY KEY  (`softwareID`)
) TYPE=InnoDB

CREATE TABLE `tblComputerSoftwareLink` (
  `softwareID` int(10) unsigned NOT NULL default '0',
  `cpuID` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`cpuID`,`softwareID`),
  KEY `softwareID` (`softwareID`),
  CONSTRAINT `FK_tblComputerSoftwareLink_1` FOREIGN KEY (`cpuID`) REFERENCES `tblComputer` (`ComputerID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_tblComputerSoftwareLink_2` FOREIGN KEY (`softwareID`) REFERENCES `tblSoftware` (`softwareID`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB

I'm thinking that two separate SQL queries are the easiest way to fill in these two fields (I've already got 13 other queries for the form, so a few more won't hurt), but I can't for the life of me get the queries right. I'm thinking about something along the lines of

SELECT * FROM tblSoftware
  LEFT JOIN tblComputerSoftwareLink ON tblComputerSoftwareLink.softwareID = tblSoftware.softwareID
WHERE tblComputerSoftwareLink.softwareID IS NULL AND tblComputerSoftwareLink.cpuID = '$computerID'

but this doesn't work. If I leave out the WHERE clause for cpuID, it works, but then it lists software which isn't installed anywhere at all. I want software that isn't installed on the computer I'm currently viewing/editing.

Help, anyone?

[r937]

when you do a LEFT OUTER JOIN and want to specify a condition on a column of the right table, this must go into the ON clause, or else it won't work

select tblSoftware.* 
  from tblSoftware
left outer
  join tblComputerSoftwareLink 
    on tblSoftware.softwareID
     = tblComputerSoftwareLink.softwareID
   and tblComputerSoftwareLink.cpuID = '$computerID'
 where tblComputerSoftwareLink.softwareID is null

[carrot]

Wow, response time: 7 minutes! Rudy, you beat any helpdesk in the world, hands down. Thanks!

[r937]

thanks for the kind words

but fast response only occurs when you happen to be smart enough to post just before i check the forum

[carrot]

Well, you give the right answers too, which is more than most helpdesks can manage.

I shall now hunker down and await the bombardment from all the helpdesk workers here.