2 Joins with same table

[mifri...@gmail.com]

Hi,

following scenario:

Table VideoserviceConnection has coloumns sinkService_id and sourceService_id, which are IDs to table Service. Service has the coloumn individual_id. I would like to join 2x service to the table VideoserviceConnection.

So I have the code:

QueryBuilder<VideoserviceConnection, Long> qb = videoserviceConnectionDAO.queryBuilder();

QueryBuilder<Service, Long> qbSinkService = serviceDAO.queryBuilder();

qbSinkService.where().eq("individual_id", individual.getId());

QueryBuilder<Service, Long> qbSourceService = serviceDAO.queryBuilder();

qbSourceService.where().ne("individual_id", individual.getId());

qb.join("sinkService_id", "id", qbSourceService);

qb.join("sourceService_id", "id", qbSourceService);

This throws

Ambiguous column name "ID"; SQL statement:

SELECT `videoserviceconnection`.* FROM `videoserviceconnection` INNER JOIN `service` ON `videoserviceconnection`.`sinkService_id` = `service`.`id` INNER JOIN `service` ON `videoserviceconnection`.`sourceService_id` = `service`.`id` WHERE `service`.`individual_id` <> 1 AND (`service`.`individual_id` <> 1 )  [90059-192]

The problem seems that ormlite didn't rename the service table, so it got problems in the WHERE-clause. It should be like

SELECT `videoserviceconnection`.* FROM `videoserviceconnection` INNER JOIN `service` sinkService ON `videoserviceconnection`.`sinkService_id` = `sinkService`.`id` INNER JOIN `service` sourceService ON `videoserviceconnection`.`sourceService_id` = `sourceService`.`id` WHERE `sinkService`.`individual_id` = 1 AND (`sourceService`.`individual_id` <> 1 ) (or similiar).

How can I achieve this?

Best regards,

Michael Fritscher