How do I write query without joining?

ljin · **Joined:** Tue Feb 13, 2007 3:31 am **Posts:** 8

Hi, this is probably a very easy question, but I somehow can't find any answer to it. I have the following two classes:

Code:

public class Teacher {
  private int id;
}

public class School {
  private int id;
  private Teacher teacher;
  private String name;
}

Now, I want to write a query that gets all teacher's ID in a particular school.

Code:

       SELECT t.id FROM School AS s INNER JOIN s.teacher AS t
       where s.name = ?

Now my question is this: why do I need to join with the Teacher table. Apparently, there is this teacherId column in the School table, so I don't really need to join with Teacher. But I don't know how to access this in HQL. If someone knows, please help. Thanks in advance.

artem · **Joined:** Fri Feb 09, 2007 6:51 pm **Posts:** 16

if you have many-to-one association, you can declare schools in Teacher.
Then it could be (AFAIK):

Code:

       SELECT t.id FROM Teacher AS t
       where t.schools.name = ?

but in resulting SQL code you, nevertheless, get inner join.

ljin wrote:

Hi, this is probably a very easy question, but I somehow can't find any answer to it. I have the following two classes:

Code:

public class Teacher {
  private int id;
}

public class School {
  private int id;
  private Teacher teacher;
  private String name;
}

Now, I want to write a query that gets all teacher's ID in a particular school.

Code:

       SELECT t.id FROM School AS s INNER JOIN s.teacher AS t
       where s.name = ?

Now my question is this: why do I need to join with the Teacher table. Apparently, there is this teacherId column in the School table, so I don't really need to join with Teacher. But I don't know how to access this in HQL. If someone knows, please help. Thanks in advance.

ljin · **Joined:** Tue Feb 13, 2007 3:31 am **Posts:** 8

Thanks, but still we get an inner join. It seems to me that it's completely pointless. Is there anyway to get past that?

artem · **Joined:** Fri Feb 09, 2007 6:51 pm **Posts:** 16

By the way, seems to me that you should do s/th like this:

Code:

public class Teacher {
  private int id;
  private School school;
}

public class School {
  private int id;
  private String name;
}

and if you have some particular school1 you could do

Code:

       SELECT t FROM Teacher AS t
       where t.school = :theschool

and set :theschool = school1

in this case, school has many teachers, not one and you have no joins at all.

ljin wrote:

Hi, this is probably a very easy question, but I somehow can't find any answer to it. I have the following two classes:

Code:

public class Teacher {
  private int id;
}

public class School {
  private int id;
  private Teacher teacher;
  private String name;
}

Now, I want to write a query that gets all teacher's ID in a particular school.

Code:

       SELECT t.id FROM School AS s INNER JOIN s.teacher AS t
       where s.name = ?

Now my question is this: why do I need to join with the Teacher table. Apparently, there is this teacherId column in the School table, so I don't really need to join with Teacher. But I don't know how to access this in HQL. If someone knows, please help. Thanks in advance.

ljin · **Joined:** Tue Feb 13, 2007 3:31 am **Posts:** 8

Hi, Artem:

Sorry, I probably had this relationship reversed. I was just trying to come up with an example of my situation. Let me try again.

Code:

public class Teacher { 
  private int id; 
  private int salary;
  private School school;
} 

public class School { 
  private int id;  
} 

Now get all school ID for teachers that get paid a certain salary

Code:

       SELECT s.id FROM Teacher AS t INNER JOIN t.school AS s 
       where t.salary < 100000 

To get all the school IDs, I figure I need to do a join with school. So the question is, is there a better way that doesn't do joins? Thanks.

artem · **Joined:** Fri Feb 09, 2007 6:51 pm **Posts:** 16

Hi,
If you choose data from one table (schools in your case) using information from another table (teacher's salary in your case) you always will get join or use nested queries (until you already have your list of teacher's ids).

IMHO you can:
1) leave it as it is (good choice);
2) reformat HQL to use subquery;
3) denormalize tables to join schools information to teacher's table.

Hope this hepled.

ljin · **Joined:** Tue Feb 13, 2007 3:31 am **Posts:** 8

Hi, Artem:

My question really is that all the information is available in one table: the Teacher table. Since the teacher table has access to schoolId , I should not have to join with the school table to find that out. Obviously, if I want to find other information about the school table, then I need the join. But all I want is the schoolId, which is available as one of the fields in the teacher table.

artem · **Joined:** Fri Feb 09, 2007 6:51 pm **Posts:** 16

Oh, may be you mean:

Code:

       SELECT t.schoolId FROM Teacher AS t
       where t.salary < 100000 

ljin wrote:

Hi, Artem:

My question really is that all the information is available in one table: the Teacher table. Since the teacher table has access to schoolId , I should not have to join with the school table to find that out. Obviously, if I want to find other information about the school table, then I need the join. But all I want is the schoolId, which is available as one of the fields in the teacher table.

ljin · **Joined:** Tue Feb 13, 2007 3:31 am **Posts:** 8

Yes, but I thought I can't access t.schoolId in the HQL. Please correct me if this is not correct.

artem · **Joined:** Fri Feb 09, 2007 6:51 pm **Posts:** 16

can try writing

Code:

        
<many-to-one name="school" class="mytest.School" update="false" insert="false" fetch="select">
            <column name="id" not-null="true" unique="true">
                <comment></comment>
            </column>
        </many-to-one>
        <property name="schoolId" type="java.lang.Integer">
            <column name="school_id">
                <comment></comment>
            </column>
        </property>

or

Code:

select t.school.id from Teacher as t where t.salary >=10

ljin wrote:

Yes, but I thought I can't access t.schoolId in the HQL. Please correct me if this is not correct.

khangharoth · **Joined:** Fri Aug 31, 2007 11:58 am **Posts:** 19

Code:

" SELECT t.school.id FROM Teacher AS t where t.salary < 100000 "

This works and no Join.

Good question to be on forum , cheers

ljin · **Joined:** Tue Feb 13, 2007 3:31 am **Posts:** 8

Thank you, this works. I knew there must be an easy answer. Thanks a lot.