Re: Making Matched Records with Primary File in Query an Outer joinin SQL

[Pete Helgren <Pete@xxxxxxxxxx>]

"Pete, make sure you double check the joins being done in query vs. the 
joins being done in SQL. "

There's the rub.  I don't know what the joins are in the query because 
all I have is a list of "where " comparisons.  That is I have "Where 
field1=field2 and field1 = field2 " etc  instead of "from  file1 join 
file2 on field1= field2 join file3 on field1 = field3"  That is what is 
leaving me scratching on head on a " Matched Records with Primary File" 
option in query.  There are no joins listed in the query from RTVQMQRY.

I guess to restate this: If there WAS a way to see the exact syntax (SQL 
Syntax) from a query, replicating that in SQL would be a snap.  As it 
is, when you have a complex query that produces the desired results in 
Query/400, you basically have to start from scratch in rebuild that 
query in SQL.  In my particular case I have a query that outputs in a 
way I expect in Query/400 but replicating that same result in SQL had 
been devilishly difficult.

I wonder how the new Web Query product is going to migrate existing 
Query/400 queries (if it indeed does do that)?  They must have a better 
tool than RTVQMQRY .

Pete

Wilt, Charles wrote:
> Note that one thing to keep in mind is the join fields being specified in Query or SQL
>
> There's two options in both:
> F1 joined to F2
> F2 joined to F3
>
> Or
>
> F1 joined to F2
> F1 joined to F3
>
> The join chosen changes the results.  For instance, with Chucks example data slightly modified (F1 has
> 1-10, F2 has 1-5, and F3 has 2,4,6,8 ) option 1 returns:
>
>      SEQNBR        SEQNBR        SEQNBR
>           1             1              -
>           2             2             2
>           3             3              -
>           4             4             4
>           5             5              -
>           6              -             -
>           7              -             -
>           8              -             -
>           9              -             -
>          10              -             -
>
> Whereas option 2 returns
> SEQNBR        SEQNBR        SEQNBR
>      1             1              -
>      2             2             2
>      3             3              -
>      4             4             4
>      5             5              -
>      6              -            6
>      7              -             -
>      8              -            8
>      9              -             -
>     10              -             -
>
> Pete, make sure you double check the joins being done in query vs. the joins being done in SQL. 
>
> HTH,
> Charles
>
>   
>