Join query in HQL

[Shinu Pil]

Hi

i have two tables abc and device

   CREATE TABLE `abc (  
      `Id` INT(11) NOT NULL AUTO_INCREMENT,  
      `empid` INT(11) DEFAULT NULL,  
      `empName` VARCHAR(500) DEFAULT NULL,  
     issueDate DATE DEFAULT NULL,  
      `deviceId` INT(11) DEFAULT NULL,  
      PRIMARY KEY (`allochardwareId`),  
      FOREIGN KEY (`deviceId`) REFERENCES `device` (`deviceId`)  
    ) ENGINE=INNODB  DEFAULT CHARSET=latin1;  

    CREATE TABLE `device` (  
      `deviceId` INT(11) NOT NULL AUTO_INCREMENT,  
      `deviceName` VARCHAR(20) DEFAULT NULL,  
        quantity INT(11) DEFAULT NULL,   
           serialNo VARCHAR(20) DEFAULT NULL
      `deviceTypeId` int(11) DEFAULT NULL,  
        PRIMARY KEY (`deviceId`),FOREIGN KEY (deviceTypeId`) REFERENCES `deviceType` (`deviceTypeId`);  
    ) ENGINE=INNODB DEFAULT CHARSET=latin1;  

my mapping file of abc i added


<many-to-one name="device" class="com.model.device" cascade="all" >
<meta attribute="default-value">new Device()</meta>
<column name="deviceId" />
</many-to-one>
My requirement is that when user search for deviceName from the uI it will fetch data first table and corresponding employee id assigned.If it not assigned need to return null value

How written in hibernate....

i written sample query in mysql it will work fine but i donot know how convert thta into HQl
It is the mysql query

SELECT *
FROM device
LEFT JOIN abc
ON device.deviceId=abc.deviceId where device.serialNo="ee" and abc.empid="6"

i wriiten corresponding hQL as

select device from Device device LEFT JOIN abc from Abc abc ON device.deviceId=abc.device.deviceId

i got following exception

rg.hibernate.hql.ast.QuerySyntaxException: unexpected token: from near line 1, column 76 [ ]
org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:281)
org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134)
org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)

please help on this

[Hebert Coelho]

Instead
LEFT JOIN abc
it should be
LEFT JOIN device.abc

This post show how to work with JPQL/HQL: http://uaihebert.com/?p=1274

[Shinu Pil]

i resolved this pblm by giving
select abc from ABC ABC LEFT JOIN abc.device
right now i getting results when matching records found in both tables ,but if there is no empid is not in abc and records are in device it not displayed.....wht will do in this situation i tried right join also but no chance to get results.Any help really appreciated..