Results 1 to 3 of 3

Thread: outer join

  1. #1
    Join Date
    Aug 2006
    Posts
    15

    Unanswered: outer join

    Hello,

    I am having problems with an outer join statement.
    I have written a procedure that tests a table for missing and corrupt data and
    to test my procedure, I take a table with 100% correct entries and corrupt them by hand. Then I test if my repaird data is looking like the correct data did.
    To do the test, I copy the correct data into a temp table "copy", join it with the "repaired" table and check if any fields look different. The problem is, that i don't get the missing data. The statement is looking like this:
    PHP Code:
    select o.*,'#',k.* from repaired o  right outer join  copy k on  
    (str(o.a) + 'A' str(o.b) + 'A' str(o.c) =str(k.a)+  'A' str(k.b) + 'A' str(k.c) )
    where 
    o
    .D<>k.or 
    o.E<>k.or 
    o.F<>k.or 
    ... 
    I have dont the concatenation because I thougt, that a join with 3 fields could be responsible for not finding the missing data in table "copy".
    Before that it looked like:
    PHP Code:
    ... on (o.a=k.and o.b=o.and o.k.cwhere ... 
    In table "copy" is a record missing that is in table "repaired".
    Why is my statement not printingout that missing record?
    Shouldn't be an outer join exactly what I have to use for finding missing data?

    I anybody can help me, I would be very happy.

    Sven

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by spjansen
    In table "copy" is a record missing that is in table "repaired".
    try LEFT OUTER JOIN instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your first join attempt:
    Code:
    ... on (o.a=k.a and o.b=o.b and o.c = k.c) where ...
    joins o.b on itself. This sort of typo happens with the gratuitous use of table aliases.
    Use a left outer join, as Rudy has suggested.
    Try using the binary_checksum value rather than listing all your columns in the where clause:
    Code:
    select	repaired.*,
    	'#',
    	copy.*
    from	--repaired
    	(select	binary_checksum(*) checkvalue,
    		repaired.*
    	from	repaired) repaired
    	left outer join --copy
    		(select	binary_checksum(*) checkvalue,
    			repaired.*
    		from	copy) copy
    		on repaired.a = copy.a
    		and repaired.b = copy.b
    		and repaired.c = copy.c
    where	repaired.checkvalue <> copy.checkvalue
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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