What would be the best way to make the following query? I want to retrieve the expedition, project, expeditionUser, and projectUser, but I'm not sure how. Does this need to be 2 queries?
Some more information might help. Such as the relationship between the tables and a little explanation of each. From the query itself, it's hard to tell.
e and p have a 1-1 relationship. e & p each have a user (u), and the user most likely will not be the same.
I am using spring jdbc. I would like to fetch e, p, and their respective users (u). Then in my RowMapper, I would use this information to populate my entities.
Rj Ewing wrote:e and p have a 1-1 relationship. e & p each have a user (u), and the user most likely will not be the same.
I am using spring jdbc. I would like to fetch e, p, and their respective users (u). Then in my RowMapper, I would use this information to populate my entities.
The query looks fine then. Is there anything amiss with it?
okay, so I've got an issue with the following query.
I would like this query to return a bcid even if it isn't found in the expeditionBcids table. This query currently works only when the bcid is in the expeditionBcid table. However a bcid isn't always present in the expeditionBcids table. If not found, the only data that should be returned is the bcid and "users bu" table. Any ideas?
It's hard to tell what you want, as you specify the bcid directly. Also, the chain of tables has an outer join in the middle of it all, yet tables are attached to it that are not outer joined. That makes for funny business even if it does work.
FWIW, i rewrote the query into something i could understand, which to me, better illustrates the issue:
You may want to join e, eu, p, and pu in a CTE first, and simple outer join that CTE to eb. That'd likely make a cleaner, easier to understand query.
So I am wanting to retrieve the bcid and any related information. The only related information that exists 100% of the time is the bcid user (bu). Thus I would like to retrieve the bcid and bcid user (bu) on every query.
Now 80% of the time, a bcid will belong to an expedition. This is mapped in the expeditionBcids table.
So if the bcid is in the expeditionBcids, I would like to retrieve the following information:
the expedition(e), expedition user (eu), project (p), project user (pu)
The reason I would like to retrieve any related information is that in my bcid entities, I have an Expedition entity property and a User entity property (similar to JPA one-to-one, and many-to-one), that I would like to populate when I create the Bcid entity object.
Rj Ewing wrote:I would like this query to return a bcid even if it isn't found in the expeditionBcids table. This query currently works only when the bcid is in the expeditionBcid table. However a bcid isn't always present in the expeditionBcids table. If not found, the only data that should be returned is the bcid and "users bu" table. Any ideas?
You'll probably need to change a few INNER JOIN clauses into LEFT or RIGHT OUTER JOIN clauses. More info about joining different tables can be found here.