>I am trying to create a select statement in Query Analyzer to join two tables together and get all of the rows from the second table and the matching rows from the first table. Here is what I have come up with:
>
>select b.township, b.range, b.section, b.stand, a.a, a.b, a.c, a.d, a.e, a.g, a.h, a.j,
>a.k, a.l, a.m, a.n, a.o, a.p, a.s, a.t, a.w, a.hardwood, a.softwood, a.total
>from lis_vol a right join InvStds b on
>a.township+a.range+a.section+a.stand = b.township+b.range+b.section+b.stand
>order by b.township+b.range+b.section+b.stand
>
>The problems I am having is that it returns 9009 rows instead of 8999 which is the row count in the right table. In theory it should only return 8999 rows. What am I missing?
It is normally wrong to compare concat varchar,
also because SQL cannot optimize the join
( except if you have an index on a computed field )
select b.township, b.range, b.section, b.stand, a.a, a.b, a.c, a.d, a.e, a.g, a.h, a.j,
a.k, a.l, a.m, a.n, a.o, a.p, a.s, a.t, a.w, a.hardwood, a.softwood, a.total
from lis_vol a right join InvStds b on
a.township =b.township
and
a.range = b.range
and
...
order by b.township,b.range,b.section,b.stand