Re: Why is this a cross join?

Tim Uckun <timuckun(at)gmail(dot)com> wrote:

>> how do you want to determine which of the matching rows in the
>> other table to choose, and which to ignore?
>
> Any of the matching rows will do but I just want one.

Well, if you don't care, this might give you what you want.

select distinct on (x.first6) crm.*, x.*
  from from sap_crm_sales crm
  join (select left(mc.sap_code,6) as first6, mc.*
          from model_configurations mc) x
    on x.first6 = left(crm.customer_class, 6)
  order by x.first6;

I can think of about a dozen different things which fit the
description that you've given so far that you *might* want instead,
but unless you get more specific we can only guess.

If this isn't what you want, your best bet might might be to come
up with a self-contained example, where you create an populate
tables with a small amount of data, and show what you would like as
a result given that data set.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company