have two tables abc and device
ABC :
Code:
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;
DEVICE
Code:
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;
Mapping file for ABC
Code:
<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 a user searches 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 from abc table but matching record from device table
It is the SQL query
Code:
SELECT *
FROM abc
LEFT JOIN device
ON device.deviceId=abc.deviceId where device.serialNo="ee"
I wrote the corresponding HQL as
Code:
select abc from ABC ABC LEFT JOIN abc.device where device.serialNo="ee"
here it returns only matching records in both tables. I need to retrieve records in such a way that even not matching also displayed with null values if record not found in another table.For example some records are available in device but not in ABC with serial no="ee" also need to displayed.
Login
FAQ
Search