Re: Why is this a cross join?

On 18/02/2013, at 7:58 AM, Tim Uckun <timuckun(at)gmail(dot)com> wrote:

>> Apparently the first 6 characters of those fields are quite common, which
>> gives you a result for every possible combination of the same 6-character
>> value.
>
>
> Mmmmm. That seems kind of weird. Is there any way to NOT have this
> be a cross join? For example if I extracted the first six characters
> into a field and then joined on them it would not be a cross join
> right?

In some way, every join is a cross join, with the results filtered according to the specificity of the join conditions. In this case:

inner join model_configurations mc on left(crm.customer_class, 6) = left(mc.sap_code,6)

"customer_class" sounds like a fairly generic sort of field, so you'd expect many matches. Truncating the fields is likely to make this even less specific, returning more results.

Cheers,

Tony