SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Finland
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT query problem...

    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:

    Code:
    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

    Code:
    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    select tblSoftware.* 
      from tblSoftware
    left outer
      join tblComputerSoftwareLink 
        on tblSoftware.softwareID
         = tblComputerSoftwareLink.softwareID
       and tblComputerSoftwareLink.cpuID = '$computerID'
     where tblComputerSoftwareLink.softwareID is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Finland
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, response time: 7 minutes! Rudy, you beat any helpdesk in the world, hands down. Thanks!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Finland
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •