|
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.