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!
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:
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?