Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38

    Unanswered: look for not exist data

    Hi guys,

    I have 4 child table that contain same fieldname. Data inside this 4 table might be redundant among the 4 table. I call it as tbl1, tbl2, tbl3 and tbl4 fieldname is id_cert. what i mean redundant is, let say in tbl1 got data 1001 maybe in tbl3 also got this id. So my problem is i want to check whatever data in this 4 tbl is not exist in master table call masTbl(fieldname to check is id_no). can I do this without using many inner join script and generate dunmp table? Pls help me on this. Thanks in advanced.

    Regards,
    Shaffiq

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select d.id_cert
      from (
           select id_cert from tbl1
           union
           select id_cert from tbl2
           union
           select id_cert from tbl3 
           union
           select id_cert from tbl4 
           ) as d
    left outer
      join masTbl
        on masTbl.id_no = d.id_cert
     where masTbl.id_no is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38
    Hi r937,

    thx for the code. It helps me a lot

    Thx

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Another way to do the same thing that often runs faster (especially on large, indexed sets of data) is:
    Code:
    SELECT Coalesce(t1.id_cert, t2.id_cert, t3.id_cert, t4.id_cert)
       FROM master AS m
       FULL JOIN tbl1 AS t1 ON (t1.id_cert = m.id_cert)
       FULL JOIN tbl2 AS t2 ON (t2.id_cert = m.id_cert)
       FULL JOIN tbl3 AS t3 ON (t3.id_cert = m.id_cert)
       FULL JOIN tbl4 AS t4 ON (t4.id_cert = m.id_cert)
       WHERE m.id_cert IS NULL
    -PatP

  5. #5
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38
    Hi Pat,
    Thx for your solution, it works fine. Thx soo much for both of you guys.

    Regards,
    Shaffiq

Posting Permissions

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