Re: SQL join question

SELECT * FROM ADAM1
....+....1..
CHAR1  CHAR2
  A     A1 
  B     B1 

SELECT * FROM ADAM2
....+....1..
CHAR1  CHAR2
  B     B2 
  C     C2 


SELECT ADAM1.CHAR1, ADAM2.CHAR1, ADAM1.CHAR2, ADAM2.CHAR2
FROM ADAM1 LEFT JOIN ADAM2 
     ON ADAM1.CHAR1 = ADAM2.CHAR1 
UNION 
SELECT ADAM1.CHAR1, ADAM2.CHAR1, ADAM1.CHAR2, ADAM2.CHAR2
FROM ADAM1 RIGHT EXCEPTION JOIN ADAM2 
     ON ADAM1.CHAR1 = ADAM2.CHAR1 
....+....1....+....2....+.
CHAR1  CHAR1  CHAR2  CHAR2
  A      -     A1     - 
  B      B     B1     B2 
  -      C     -      C2 


Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




"Adam Lang" <aalang@xxxxxxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/26/2003 02:15 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<midrange-l@xxxxxxxxxxxx>
cc

Subject
SQL join question






Ok, say you have two tables

TABLE1:
pol_num       build_value
1                     10
3                      15
5                       11

TABLE2:
pol_num       auto_value
2                     7
4                      1
5                       21


How would you get a result set that would include all polnum in both 
tables,
but with both values if the policies match?  The closest I can get is 
using
a left join.

SELECT T1.polnum, T1.build_value, T2.auto_value
 FROM T1
 LEFT JOIN T2
 ON T1.polnum = T2.polnum

but that won't give me pol_num 2 and 4 in table2.

Any ideas?

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.