• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

help with select query

 
Rj Ewing
Ranch Hand
Posts: 91
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Rj Ewing
Ranch Hand
Posts: 91
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Rj Ewing
Ranch Hand
Posts: 91
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ah, it wasn't working because of a different error. I found the issue, and it works as expected. Thanks
 
Rj Ewing
Ranch Hand
Posts: 91
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Rj Ewing
Ranch Hand
Posts: 91
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Sorry for not being clear
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic