Left join, cross-joined view, running forever

3 Jan 2005 9:13 AM

Viren

Hi All,

I am using VB.Net on a Pervasive.SQL database.

I am trying to retrieve all Customers and Items, and only the related Sales
for these Customers/Items. Because there isn't a link between the customer
and items, I've created a cross joined view of the customers and items, which
returns all the customers and items. I'm trying to left join specific
customer and items to Sales for a specific period; however this query is
taking forever to return my recordset.

Any ideas, for a simpler and/or more efficient solution.

Regards,

Viren

3 Jan 2005 11:22 AM

Uri Dor

what about "customers left join sales left join items" ?
cross joins are evil

Viren wrote:

Show quoteHide quote

> Hi All,
>
> I am using VB.Net on a Pervasive.SQL database.
>
> I am trying to retrieve all Customers and Items, and only the related Sales
> for these Customers/Items. Because there isn't a link between the customer
> and items, I've created a cross joined view of the customers and items, which
> returns all the customers and items. I'm trying to left join specific
> customer and items to Sales for a specific period; however this query is
> taking forever to return my recordset.
>
> Any ideas, for a simpler and/or more efficient solution.
>
> Regards,
>
> Viren

3 Jan 2005 11:49 AM

Viren

Unfortunately, as I stated previously, there is no link between the customer
and item table, and also I want to see all items for all customers, and the
aggregated sales for all linked customers/items, as well as zero-sales for
those that aren't linked.

Show quoteHide quote

"Uri Dor" wrote:

> what about "customers left join sales left join items" ?
> cross joins are evil
>
> Viren wrote:
>
> > Hi All,
> >
> > I am using VB.Net on a Pervasive.SQL database.
> >
> > I am trying to retrieve all Customers and Items, and only the related Sales
> > for these Customers/Items. Because there isn't a link between the customer
> > and items, I've created a cross joined view of the customers and items, which
> > returns all the customers and items. I'm trying to left join specific
> > customer and items to Sales for a specific period; however this query is
> > taking forever to return my recordset.
> >
> > Any ideas, for a simpler and/or more efficient solution.
> >
> > Regards,
> >
> > Viren
>

3 Jan 2005 2:54 PM

Patrice

No link ? How do you know which items a customer ordered ?
If the DB is not yours, check to see if you don't have a "link" table. Ie.
the relation is likely stored in its own table allowing a customer to order
multiple items and an item to be ordered by multiple customers...

The cross join will return all possible combinations i.e. num customers x
num items records, likely not what you want.

Patrice

--

Show quoteHide quote

"Viren" <Vi***@discussions.microsoft.com> a écrit dans le message de
news:36044EDA-240C-4414-A3B4-978E57972032@microsoft.com...
> Unfortunately, as I stated previously, there is no link between the
customer
> and item table, and also I want to see all items for all customers, and
the
> aggregated sales for all linked customers/items, as well as zero-sales for
> those that aren't linked.
>
> "Uri Dor" wrote:
>
> > what about "customers left join sales left join items" ?
> > cross joins are evil
> >
> > Viren wrote:
> >
> > > Hi All,
> > >
> > > I am using VB.Net on a Pervasive.SQL database.
> > >
> > > I am trying to retrieve all Customers and Items, and only the related
Sales
> > > for these Customers/Items. Because there isn't a link between the
customer
> > > and items, I've created a cross joined view of the customers and
items, which
> > > returns all the customers and items. I'm trying to left join specific
> > > customer and items to Sales for a specific period; however this query
is
> > > taking forever to return my recordset.
> > >
> > > Any ideas, for a simpler and/or more efficient solution.
> > >
> > > Regards,
> > >
> > > Viren
> >