>>Here is the SQL Select that I found works. I am not 100% sure it is the correct one but it has no NULL and returns the query. Here is this SQL select:
>>
>>
>>select equipment.equip_id, equipment.equip_desc, trandetail.* from trandetail
>> left join orders on orders.order_no = trandetail.order_no and trandetail.order_no > 0 and LEN(ordersequip_id) > 0
>> join equipment on (trandetail.equip_pk > 0 and equipment.equip_pk = trandetail.equip_pk) or
>> (trandetail.order_no > 0 and equipment.equip_id = orders.equip_id)
>>
>>
>>The key is that I have JOIN EQUIPMENT only once and have it with an OR. If you see anything I am doing wrong, please let me know.
>>Thank you.
>>
>
>If it works it works... Your SQL does not guarantee embolded part of original text -result is correct only due to the fact that the second (inner) join finds always a matching item for trandetail, which is something you know or is/has to be guaranteed by RI.
>
>To be totally certain would be to do the second join also as left join and check any resulting lines with null or check each line is really mirrored by doing a select count(*) from trandetail where trandetail.pk not in (select result.pk from result)
>
>Not knowing the table stability and need, hard to argue as a necessity ;-)
>
Thank you, Thomas. I did some tests today and all worked. But I will do more testing tomorrow, when I am less tired, or less tipsy :) (Just came home from a restaurant).
"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