>>I am not sure that UNION is the clause I need in this case. Once I "moved" the WHERE clause into JOIN, all works well now.
>>Thank you for your suggestion.
>>
>>>You can use UNION clause in your select, e.g. first part would select your data and second would be a UNION to Parts only (no need to do joins).
>
>Interesting. I would not think, moving where clause to JOIN would work...
It was strange to me too that it works. Because I have never before used this syntax. I thought that JOIN can only use something like
ON TABLE1.FIELD = TABLE2.FIELD.
I hope I am not doing something that I will later regret when using syntax:
Select * from MYTABLE1 join MYTABLE2 on MYTABLE1.FLD1 = MYTABLE2.FLD2 and MYTABLE2.SOMEDATE between (BEGINDATE and ENDDATE)
I hope someone who knows more about correct SQL Select will warn me of the potential problem.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
