Hi,

Iam trying to join two tables and retrieve some fields using sql.

i have used session.createSqlQuery("query");

Strangely in the select clause of the query if i use * ,the above code dosent throw any error and is working fine..but if i specify only one or two fields in the select clause it throws a column not found error

like for example select * from Cats cats,Dogs dogs where cats.catid = dogs.dogId is working fine

but if i use select cats.catsid,dogs.dogId from Cats cats,Dogs dogs where cats.catid = dogs.dogId is throwing a exception

can any one help me out in this..?

Hi


In createSQLQuery you have to select all properties of a given class. You have two options

1. write your query as follws

select cats.Catsid as {cats.catsid}, dogs.DogId as {dogs.dogId }, null as {cats.otherprop1} from Cats cats,Dogs dogs where cats.catid = dogs.dogId


2.(recomanded)

List list=sess.createCriteria(Cat.class,"c")
.createCriteria("Dog","d")
.setProjection(Projections.projectionList() .add(Projections.property("d.dogId")) .add(Projections.property("c.catsid")
)
).list();

Thanks for the reply amily..!

But won't it be a performance hit
since in both the cases the entire fields in the table has to be fetched and it boils out to be the same as select * from table..?

Hi

No You are wrong
in case 2 only select d.dogId and c.catsid
thats why i recomand it

Amila

(Don't forget to rate if helps)