Hi,

I have a Customer.

A Customer can have a bought item (1:m relation) and a sold item (1:m relation)

Bought Item and Sold Item have totally different tables, so that's why they're in different tables.

I have a Order table. This Order table contains an ID which can be either of a sold item or a bought item and a column that determines what kind of item it is.

Now I have a to find out the customers that have an order in for example the last week

So this is a DISTINCT on Customer. Then I have an Inner Join with Sold Item and an inner join with Bought Item. Now I can't put an inner join on order ... and so I am stuck ...

Can I do this in one query? (wanted solution)

Or should I fill the collection twice (is that possible?)? (if necessary ...)

Best regards,

G.I.

Hi, could you post the DDL and some example rows?

Just some basic info to give you the idea ... :

[b]Customer:[/b]
CustomerNo
CustomerName
CustomerAddressID
...

[b]BoughtItem:[/b]
ItemID
Description
Condition
CustomerNo
...

[b]SoldItem:[/b]
ItemID
Description
Category
CustomerNo
...

[b]Order:[/b]
OrderID
ClientID
ItemID
ItemTypeID
Amount
Date
...

[b]ItemType:[/b]
ItemTypeID
Description
...

Customer in this case is the customer that has sold an item to a client in order of bought an item from a client ...

What I need: A filled EntityCollection<CustomerEntity> with BoughtItems collection and SoldItems collection prefetched (using adapter).

I only now the date in the order table and the customerID. The ItemID can be both the ID from a Sold item and the ID of a bought item ... ItemTypeID can be used to tell the difference between bought or sold item.

I know it's a bit weird construction, but it's all inherited from a previous versions of the application ... blah blah blah headache .... can't change much in the database model either otherwise I would have done that

My Customercollection should only contain customers that have an order in a specific date range.

Only thing I can come up with is two subqueries on both sold item and bought item, and do a select after that saying SELECT .... WHERE (CustomerID IN (subquery1)) OR (CustomerID IN (subquery2)) ... and I am hoping there is a better construction ...

EDIT: Might it be possible to run two queries and fill the same collection: I mean I can run a query with inner joins on boughtitem and fill a collection and after that run the second query on solditems and fill the same collection ... does this work and update the current customers in the collection or will I get two of the same customer entities with each a filled relation collection (1 with Sold and 1 with Bought) ???

Might it be possible to run two queries and fill the same collection: I mean I can run a query with inner joins on boughtitem and fill a collection and after that run the second query on solditems and fill the same collection ... does this work and update the current customers in the collection or will I get two of the same customer entities with each a filled relation collection (1 with Sold and 1 with Bought) ???

Yes, you may append to the same entityCollection. Just make sure that the entityCollection's property DoNotPerformAddIfPresent is set to true (default=true). Also you can use a Context to store the entityCollection to make sure you have one instance only of each entity, thus the fetched related entities/collections get added to the same entity instance in that context.

thanks, I am looking into Context now ... have never used it before