>Hi,
>
>I see the following behavior of SQL Select, when both parent and child tables have the same field name and joined in SQL Select. And the value is set in the column that is shown first in the SQL Select order. Here is an example:
>
>
>select ChildTable.FIELD_X, ParentTable.* from ParentTable JOIN ChildTable on ParentTable.Pk = ChildTable.Pk
>
>Note that the ParentTable also has field FIELD_X but the resulting query shows the value of FIELD_X from the ChildTable.
>Is this by design that since the column FIELD_X shows before the ParentTable.* it takes precedence?
>
>TIA
Hi Dmitry,
I am afraid I might not have understood what you are saying. You are asking ChildTable.Field_x plus all the fields from ParentTable. It would do exactly that. There would be 2 'field_x' columns in the result it returns and the first column would have the values of ChildTable.field_x, while second having values from ParentTable.field_x. Isn't that what you are seeing? i.e.:
ParentTable:
pk field_x
1 1
2 2
3 3
ChildTable:
pk field_x
1 1C
2 2C
3 3C
Query:
field_x pk field_x
1C 1 1
2C 2 2
3C 3 3
(If it were a VFP query, then the columns would be renamed as Field_x_a and Field_x_b respectively. MS SQL doesn't do this renaming and thus any subsequent selection from this result is problematic)
One solution is to explicit renaming of the colliding fields. i.e.:
select ChildTable.FIELD_X as ct_Field_x, ParentTable.* from ParentTable JOIN ChildTable on ParentTable.Pk = ChildTable.Pk
However, it is always suggested not to use * but list all fields explicitly.