• 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
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

how to write this query

 
Ranch Hand
Posts: 97
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Everyone,

Being new to EJB QL, I'm unable to figure this out. Please help. I'll provide a simplified scenario.
You have a customer table with columns: name, state. state is a lookup/fk value like 11,12 where the full description comes from another table STATE having colms state_id, state_desc.
How do I write this query in EJB3 QL so that I get back the customer name and state description?

I don't prefer doing a join like:
Select c.name, s.desc from customer c, state s where c.state=s.state_id. I think thats easy to figure out in EJB QL.

The reason I don't want this is that I could have 10 other lookup columns in my customer table and I don't want to go on doing joins. (Actually in my case I have one lookup table to store all lookup values so it makes it complicated!!).

I'd prefer an equivalent of something like this:
Select c.name, (Select s.desc from state s where s.state_id=c.state) from customer c.

I'm not sure how to do this.
Please suggest and guide.. Tks!
 
author & internet detective
Posts: 40791
828
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Raj Bhandari:
Select c.name, (Select s.desc from state s where s.state_id=c.state) from customer c.


This is still a join, just written differently.

Why don't you want to a join? Do you have a known performance problem? Joins on small tables rarely take a long time. And for small tables, you would add an index to speed things up.
 
Raj Bhandari
Ranch Hand
Posts: 97
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Jeanne,

Tks for the reply.
If thats the case, I have the following doubts:

1. Is it possible to run this query anyhow using EJB QL:

Select c.name, (Select s.desc from state s where s.state_id=c.state) from customer c ?

2. If you are suggesting this sql instead: Select c.name, s.desc from customer c, state s where c.state=s.state_id; what would you suggest in the case when there's an additional column say country_id in customer table. How would the query be so that I get customer name, state description, country description? Here are my tables:



Tks
Raj
[ June 24, 2008: Message edited by: Raj Bhandari ]
 
Raj Bhandari
Ranch Hand
Posts: 97
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok, so I tried this:

Created an Entity like this:


I also wrote a native sql query and am able to get the values.
But now my problem is that when I try to insert Customer using
manager.persist(customer) it tries to insert stateDesc also but there's no column as such in customer table but its just for search resultset.
Here's my table again:



Now if I make stateDesc @Transient so that the insert and update ignores then my select query doesn't put value in there. How do I get around this?

Tks
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic