Hi,

I have a database with the following tables:

Cars <--- PartsByCar ---> Parts

That means that a Car can have many Parts and a Part can belong to many Cars (many to many relationship).

The thing is that I need to build a query that returns all the cars that have a certain number of Parts all at once, for example: Part 1 AND Part 2 AND Part 3.

I already resolved it with the "OR" operator, but that is not what I need.

I was supposed to finish this more than a week ago and tomorrow is my last chance, so I will really appreaciate your help!!

Thanks!

Marcelo

I'm using LLBLGen Pro 2.5 and Self Servicing.

Hi marcelo,

The thing is that I need to build a query that returns all the cars that have a certain number of Parts all at once, for example: Part 1 AND Part 2 AND Part 3.

I don't understand this. Could you please elaborate more, or post the approximate SQL or the approximate expected results?

OK, I'll give you more details on the tables:

Cars

CarId (PK) ModelCode

Parts

PartId (PK) PartNumber Description

PartsByCar

PartId (FK references Parts(PartId) CarId (FK references Cars(CarId)

Some sample data:

Car - ModelCode 001 has the following Parts: * PartNumber: 111 * PartNumber: 112

Car - ModelCode 002 has the following Parts: * PartNumber: 111 * PartNumber: 666

I want to retrieve a CarCollection with all the cars that, for example, use PartNumber 111 AND PartNumber 666.

With the sample data above, this CarCollection should return Car - ModelCode 0002 only.

I hope it is clearer now, Thank!

Hi Marcelo, thanks for clarify that to me

I think this would be an approximate code for what you are trying to achieve:

CarCollection orders = new CarCollection();
IPredicateExpression filter = new PredicateExpression();

// relations to use at subqueries. This is required as you are filtering on PartNumber (at PartEntity) 
RelationCollection rels = new RelationCollection(PartsByCarEntity.Relations.PartEntityUsingPartId);

// partnumber 111
filter.AddWithAnd( new FieldCompareSetPredicate(
     CarFields.CarId, 
     ParstByCarFields.CarId, 
     SetOperator.In, 
     PartFields.PartNumber == 111, 
     rels) );

// partnumber 666
filter.AddWithAnd( new FieldCompareSetPredicate(
     CarFields.CarId, 
     ParstByCarFields.CarId, 
     SetOperator.In, 
     PartFields.PartNumber == 666, 
     rels) );

// more partnumbers ...

// fetch results
orders.GetMulti(filter);