• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

help with select query

 
Ranch Hand
Posts: 120
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?

 
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 120
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 120
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 120
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 120
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
We're being followed by intergalactic spies! Quick! Take this tiny ad!
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic