Left Outer Join

[Guest.Visitor]

kapil1 wrote: > I know to put left outer join by using SQL > Can you please tell me is it possible to use left outer join through > WRKQRY tool. Kapil, On the Join Type screen, you have 3 possible join types. Type 2 would equate to a left outer join where the first listed table is the left table. Type 3 is a left exception join. Bill

[Guest.Visitor]

Left Outer Join

Personally I recommend SQL but here's something you might like if you've ever needed to join with fields with differing attributes. It also may answer your issue. We had an issue with a 14 Character field with a customer number that is only 6 long in another file. You cannot do the substring in the "MATCHING" field. So File 1 has a 14 character customer field. File 2 has a 6 character customer field that has multiple records. To process this type of request here's the process. Input your two files you wish to join. File1 (CCCUNO) 14 character customer # File2 (ARMBNB) 6 character Customer # Next the type of join: Specify the Type of join as 1 - Matched Records. Specify How to join the files as *ALL. in the First Field: Field Test Field *ALL _____ _______________ Now go to your define fields and define a work field to match the attributes of the file you're trying to join to. In my example we defined it like this in the define result fields screen: Field Expression Column Heading Len Dec CUST# SUBSTR(T01.CCCUNO, 9,6) Now in the Select records you would put: T02.ARMBNB EQ CCMBRNO ----------------------------------------- This will do your left outer join Hope this helps... Bruce.

[kapil1]

Left Outer Join

Thanks Bruce It helps me Thanks Kapil

[kapil1]

Left Outer Join

Dear All, I know to put left outer join by using SQL Can you please tell me is it possible to use left outer join through WRKQRY tool. Is it possible??? Thanks Kapil

[T.Holt]

Left Outer Join

I can't get your technique to work, Bruce. First, I have a question: what is CCMBRNO?

T02.ARMBNB EQ CCMBRNO

Should CCMBRNO be CUST#? When you key *ALL in the join fields blank, you're telling Query you want to use a cross join (AKA Cartesian product). This joins each row from the first table to each row of the second table. If the first table has 5 rows and the second table has 7 rows, you'll get 35 rows. AFAIK, this is the maximum number of rows that can possibly be returned by the join. When you add the record selection expression, you will probably reduce the number of returned rows. Assuming the 5- and 7-row tables in the last example, suppose one of the rows of the first table matches all 7 rows of the second table and the other four rows of the first table don't match anything in the second table. The resulting joins return the following row counts. Cross join: 35 rows Left inner join: 7 rows Left outer join: 11 rows As best I can tell, your Query technique will only return 7 rows, but I hope I am overlooking something & that you will show me something new.