RE: Yet another SQL newbie question

I read Richard's post to be examples of the things to which you would apply the 
union - an example of getting matching records and one of getting only the 
fields from the first file by using the exception join. The key thing there is 
to qualify the "all fields" asterisk with the file (alias) name.

-------------- Original message -------------- 

> A join is not the same as a union. 
> 
> For example 
> select * from jhhl1 
> union 
> select * from jhhl2 
> returns 
> ....+....1.... 
> MYKEY MYCHAR 
> A Test A 
> B Test B 
> C Test C 
> D Test D 
> E Test E 
> F Test F 
> ******** End 
> 
> While 
> select a.*, b.* from jhhl1 a 
> join jhhl2 b on a.mykey=b.mykey 
> returns nothing 
> ....+....1....+....2....+....3....+. 
> MYKEY MYCHAR MYKEY MYCHAR 
> ******** End of data ******** 
> 
> Summary: A union returns separate rows from each table while a join 
> combines rows from multiple tables into a single row. 
> 
> 
> Hey, I noticed that if I change the contents of jhhl3 from 
> ....+....1... 
> MYKEY MYCHAR 
> C Test 1 
> F Test 2 
> ******** End 
> to 
> ....+....1... 
> MYKEY MYCHAR 
> C Test C 
> F Test F 
> ******** End 
> 
> Then I can do 
> select * from jhhl1 
> union 
> select * from jhhl2 
> except 
> select * from jhhl3 
> 
> and get 
> ....+....1... 
> MYKEY MYCHAR 
> A Test A 
> B Test B 
> D Test D 
> E Test E 
> ******** End 
> 
> However, if I leave MYCHAR back at their original values then I get six 
> rows. EXCEPT and INTERSECT are in the section "What's new for V5R3 in the 
> SQL Reference Book". Looks like you'll have to use my earlier suggestion 
> for obsolete versions of the operating system. 
> 
> Rob Berendt 
> -- 
> Group Dekko Services, LLC 
> Dept 01.073 
> PO Box 2000 
> Dock 108 
> 6928N 400E 
> Kendallville, IN 46755 
> http://www.dekko.com 
> 
> 
> 
> 
> 
> "Richard Casey" 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx 
> 11/23/2005 02:03 PM 
> Please respond to 
> Midrange Systems Technical Discussion 
> 
> 
> To 
> "Midrange Systems Technical Discussion" 
> cc 
> 
> Fax to 
> 
> Subject 
> RE: Yet another SQL newbie question 
> 
> 
> 
> 
> 
> 
> James, 
> 
> If INTERSECT means to select records from one file that have matching 
> records in another file, you can use an INNER JOIN. 
> 
> SELECT A.* FROM FILE1 
> INNER JOIN FILE2 B ON (A.FIELD1=B.FIELD1 AND A.FIELD2=B.FIELD2 ...) 
> 
> That will select all the records from FILE1 that have a matching record in 
> FILE2. 
> 
> If EXCEPT means to select records from one file that do NOT have matching 
> record in another file, you can use an EXCEPTION JOIN. 
> 
> SELECT A.* FROM FILE1 
> EXCEPTION JOIN FILE2 B ON (A.FIELD1=B.FIELD1 AND A.FIELD2=B.FIELD2 ...) 
> 
> That will select all the records from FILE1 that do NOT have a matching 
> record in FILE2. 
> 
> Hope this helps! 
> Richard 
> 
> -----Original Message----- 
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of James H H Lampert 
> Sent: Wednesday, November 23, 2005 1:37 PM 
> To: midrange-l@xxxxxxxxxxxx 
> Subject: Yet another SQL newbie question 
> 
> 
> My fellow geeks: 
> 
> My SQL project is progressing nicely, but I've run into 
> yet another place where I'm at a bit of a loss. 
> 
> I need to be able to UNION or INTERSECT an arbitrary 
> number of identically-structured files, and I need to, at 
> times, EXCEPT records from that intersection or union, 
> based on the records in yet another identically-structured 
> file. 
> 
> I note that AS/400 SQL, at least at V4, has a UNION, but 
> not an INTERSECT or an EXCEPT. Can anybody suggest where I 
> should be looking for an alternate way to do this? 
> 
> -- 
> JHHL 
> 
> 
> -- 
> 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. 
> 
> 
> -- 
> 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. 
>