SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: non-match query

  1. #1
    SitePoint Zealot jsk137's Avatar
    Join Date
    Jan 2002
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    non-match query

    I have three tables: jobnames, owners, and jobname_owner. Owners are assigned to jobnames using the jobname_owner table.

    Question:
    Given a jobnames.id, I need to do a query that gives me the owners.id that are NOT assigned to that jobname.

    I greatly appreciate you help with this. The table structures are included below.

    --
    -- Table structure for table `jobnames`
    --

    CREATE TABLE `jobnames` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `jobname` varchar(255) NOT NULL,
    `jobnamedisplay` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

    -- --------------------------------------------------------

    --
    -- Table structure for table `jobname_owner`
    --

    CREATE TABLE `jobname_owner` (
    `jobname_id` int(11) NOT NULL,
    `owner_id` int(11) NOT NULL,
    PRIMARY KEY (`jobname_id`,`owner_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    -- --------------------------------------------------------

    --
    -- Table structure for table `owners`
    --

    CREATE TABLE `owners` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `first` varchar(255) NOT NULL,
    `last` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jsk137 View Post
    Given a jobnames.id, I need to do a query that gives me the owners.id that are NOT assigned to that jobname.
    let's say the given id is 937

    try this query:
    Code:
    SELECT owners.id   
         , owners.first
         , owners.last 
      FROM owners
    LEFT OUTER
      JOIN jobname_owner 
        ON jobname_owner.owner_id   = owners.id
       AND jobname_owner.jobname_id = 937
     WHERE jobname_owner.owner_id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •