Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: find missing id's

    Hello everybody,

    I am new to databases and have got a little problem and just want to have a hint.

    There are three tables:
    CREATE TABLE disc_rul
    (
    ri_id character varying(20) NOT NULL,
    description character varying(50)
    );

    CREATE TABLE disc_gq
    (
    gq_id character varying(20) NOT NULL,
    description character varying(50)
    );

    CREATE TABLE disc_lin
    (
    dl_id character varying(20) NOT NULL,
    ri_id character varying(20),
    gq_id character varying(20),
    total character varying(20),
    );

    I want to find out all rows in disc_lin where there is no associated row in disc_gq or disc_rul. How is this possible?


    Thanks and best regards
    Tobias

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Either use NOT IN or LEFT JOIN WHERE NULL.

    Code:
    SELECT *
    FROM disc_lin
    WHERE ri_id NOT IN ( SELECT ri_id FROM disc_rul )
    OR  -- or AND depending on your needs
     gq_id NOT IN ( SELECT gq_id FROM disc_gq )

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    Thank you so much!
    So easy, but so hard for me ..

    Thanks & best regards
    Tobias

  4. #4
    Join Date
    Sep 2009
    Posts
    64
    Another way to solve this and not using sub select is as below:

    Code:
    SELECT
    	dl.*
    FROM
    	disc_lin dl
    	LEFT JOIN disk_rul dr ON dr.ri_id = dl.ri_id
    	LEFT JOIN disk_gq dg ON dg.gq_id = dl.gq_id
    WHERE
    	dr.ri_id IS NULL
    	OR dg.gg_id IS NULL

Posting Permissions

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