SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict Feudalism's Avatar
    Join Date
    Jun 2002
    Location
    Buenos Aires, Argentina
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question With no Join match

    Code SQL:
    CREATE TABLE `detalles_de_pedido` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `pedido` INT(11) NOT NULL DEFAULT '0',
      `cantidad` INT(11) NOT NULL DEFAULT '0',
      `folleto` INT(11) NOT NULL DEFAULT '0',
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4693 ;
     
    CREATE TABLE `pedidos` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `de` INT(11) NOT NULL DEFAULT '0',
      `estado` INT(11) NOT NULL DEFAULT '0',
      `transporte` VARCHAR(255) DEFAULT NULL,
      `recepcion` VARCHAR(50) DEFAULT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4060 ;

    these two tables are related via the detalles_de_pedidos.pedido field. Im used to make a LEFT OUTER JOIN but now i need to select all the pedidos.id that have no detalles_de_pedidos attached.

    How can I do this?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it's done with a LEFT OUTER JOIN and IS NULL check
    Code:
    SELECT pedidos.id
      FROM pedidos
    LEFT OUTER
      JOIN detalles_de_pedidos
        ON detalles_de_pedidos.pedido = pedidos.id
     WHERE detalles_de_pedidos.pedido IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict Feudalism's Avatar
    Join Date
    Jun 2002
    Location
    Buenos Aires, Argentina
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    perfectly right! I really thank you r937, you are always rescuing me.


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
  •