Hello all,

I'm getting duplicate grandparents when querying the top of a one-to-many, then many-to-many relationship.

Hibernate version: 3.1.2

Code between sessionFactory.openSession() and session.close():
return session.createQuery( "select v from Vendor v "
+ "inner join v._projects as p "
+ "inner join p._users as u "
+ "where u._id = ?" ).setLong( 0, _user.getId() ).list();

Name and version of the database you are using: MySQL (community) 5.0.12

The generated SQL (show_sql=true):
select vendor0_.vendor_id as vendor1_12_, vendor0_.vendor_name as vendor2_12_ from vendor vendor0_ inner join project _projects1_ on vendor0_.vendor_id=_projects1_.vendor_id inner join user_project _users2_ on _projects1_.project_id=_users2_.project_id inner join user user3_ on _users2_.user_id=user3_.user_id where user3_.user_id=?
(or)
SELECT v.vendor_id, v.vendor_name FROM vendor v
INNER JOIN project p ON v.vendor_id = p.vendor_id
INNER JOIN user_project up ON p.project_id = up.project_id
INNER JOIN user u ON up.user_id = u.user_id
WHERE u.user_id = ?

Some other stuff I should probably include, excerpts for brevity
@Entity @Table(name="vendor") public class Vendor {
@Id @Column(name="vendor_id") private Long _id;
}

@Entity @Table(name="project") public class Project {
@Id @Column(name="project_id") private Long _id;
@Basic @Column(name="vendor_id",nullable=false) private Long _vendorId;
@ManyToOne @JoinColumn(name="vendor_id",nullable=false,insertable=false,updatable=false) private Vendor _vendor;
@ManyToMany(mappedBy="_projects",targetEntity=User.class) private Set<User> _users = new HashSet<User>();
}

@Entity @Table(name="user") public class User {
@Id @Column(name="user_id") private Long _id;
@ManyToMany(targetEntity=Project.class) @JoinTable(name="user_project",joinColumns={@JoinColumn(name="user_id")},inverseJoinColumns={@JoinColumn(name="project_id")}) private Set<Project> _projects = new HashSet<Project>();
}

More
So I have a table of vendors, and a table of projects, with a one-to-many between those. I have a table of users, with a many-to-many relationship between projects and users (user_project with 2 foreign keys and nothing else). I'm getting duplicate vendors returned in the list. Ordinarily, I'd just do:

SELECT v.vendor_id, v.vendor_name FROM vendor v
INNER JOIN project p ON v.vendor_id = p.project_id
INNER JOIN user_project up ON p.project_id = up.project_id
WHERE up.user_id = ?

which returns distinct records from the vendor table. Generally speaking, I want a list of vendors whose projects the current user is working on.

Now maybe it's just confusion more than anything, but I haven't actually mapped the user_project table to anything, I've only mapped the vendor, project and user tables. I'm wondering if I should:
1) Use a distinct clause to return distinct vendors? (ugh)
2) Make a new class UserProject and map it to the user_project table, then use that in the query instead?
3) Look what's behind door #3?

Thanks!