Making complex join with many-to-many relationship

Making complex join with many-to-many relationship

Post by Potiguar F » Sat, 13 Sep 2003 03:27:11

Hi all.

I am wondering if it's possible to build up a DataView on a DataTable, with
a filter that shows only rows NOT related to others in a many-to-many
relationship. Example:

I have one DataTable
Customers with fields: CustomerId and CustomerName
another DataTable:
Products with fileds: ProductId and ProductName
another DataTable
Invoices with fields: InvoiceId, CustomerId, ProductId and InvoiceDate

I would Like to build a DataView on DataTable Customers, selecting only
those customers that don't have invoices containing the ProductId 32. What
should I do?

In plain SQL, I would go like:
SELECT * FROM Customers where CustomerId not in (Select Distinct
CustomerId from Invoices where ProductId = 32)

But I have no idea how to build this query in a "disconnected data" world.
Is it possible to build such a view? What DataRelations should I create? How
would be the sintax for the RowFilter property?