Working with MySQL Statement

This is heart of your module , from this query module are built up . remember if you want to display/show any field from table database , you need to include field on your query
Query are splited to 3 part , SQL select , SQL Where , SQL Group

Ilustration you have table customers with following field :

CREATE TABLE `customer` (
  `CustomerId` int(11) NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(40) CHARACTER SET utf8 NOT NULL,
  `LastName` varchar(20) CHARACTER SET utf8 NOT NULL,
  `Company` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
  `Address` varchar(70) CHARACTER SET utf8 DEFAULT NULL,
  `City` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
  `State` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
  `Country` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
  `PostalCode` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `Phone` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
  `Fax` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
  `Email` varchar(60) CHARACTER SET utf8 NOT NULL,
  `SupportRepId` int(11) DEFAULT NULL,
  PRIMARY KEY (`CustomerId`),
  KEY `IFK_CustomerSupportRepId` (`SupportRepId`)
) ENGINE=MyISAM AUTO_INCREMENT=60 DEFAULT CHARSET=latin1

EmployeeId, FirstName , LastName , Title, ReportsTo, BirthDate ,HireDate, Address ,City , State , Country , PostalCode , Phone , Fax , Email. 

At the firs time creating module using 'employee' table , generator will create single query line :
SQL Select :

SELECT employee,* FROM employee

SQL Where :

 WHERE employee.EmployeeId IS NOT NULL

SQL Group :

Why LCRUD Creating automatic " WHERE employee.EmployeeId IS NOT NULL " ? this is for prevent when users submit search form
 The result will look like :

Join Query

Now lets trying making simple join query , as you can see "ReportsTo" field is displaying number reference to EmployeeId
We plan to change  "ReportsTo" to employee name  .

 
SELECT employee.*, b.FirstName AS ReportName FROM employee 
LEFT JOIN employee AS b ON employee.EmployeeId = b.ReportsTo

Save this new query statment . next go to table tab hide ReportsTo field and display ReportName