SQL Query builder utility

Question

I have a Set of columns and Tables, in respective drop downs, I am working on a Code to generate a dynamic SQL based on the Table-Column selection

It's working in case of simple Select statements but in the case of Multiple Joins, I am trying to figure out a Syntax for handlin Right and Left Joins.

Please help..this is the Error for SQL Syntax

1)
(Select dbo.Employee.Dept_ID,dbo.Employee.Emp_ID,dbo.Employee.Emp_Name,dbo.Employee_DataVal.DeptNo,
dbo.Employee_DataVal.EmpName,dbo.Employee_DataVal.EmpNo,dbo.Employee_DataVal.Salary,dbo.Emp_Sal.Emp_ID,dbo.Emp_Sal.Salary
FROM Employee
INNER JOIN Employee_DataVal
ON Employee.Dept_ID = Employee_DataVal.DeptNo
OR Employee_DataVal.EmpName = Employee.Emp_Name)
LEFT JOIN Emp_Sal
ON Employee.Emp_ID = Emp_Sal.Emp_ID

Incorrect syntax near the keyword 'LEFT'.

2)Select dbo.Employee.Dept_ID,dbo.Employee.Emp_ID,
dbo.Employee.Emp_Name,dbo.Employee_DataVal.DeptNo,
dbo.Employee_DataVal.EmpName,dbo.Employee_DataVal.EmpNo
,dbo.Emp_Sal.Emp_ID,dbo.Emp_Sal.Salary
FROM Employee INNER JOIN Employee_DataVal
ON Employee.Emp_ID = Employee_DataVal.EmpNo
AND Employee.Dept_ID = Employee_DataVal.DeptNo
LEFT JOIN Employee
ON Employee_DataVal.EmpName = Employee.Emp_Name

The objects "Employee" and "Employee" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

PS: Running this sql on SQL server

Answer 1

This is a common problem when working with complex dynamic SQL strings on a string basis - the correct handling of the SQL syntax in its string form is difficult, and it is easy to create SQL injection vulnerabilities as well.

SQL builder APIs like jOOQ and others are very well suited for this task. I'm not sure what exactly the problem was in your case, but let's just assume that the last LEFT JOIN is optional in your query. You could write a query like this:

List<Field<?>> c = new ArrayList<>(Arrays.asList(
    EMPLOYEE.DEPT_ID,
    EMPLOYEE.EMP_ID,
    EMPLOYEE.EMP_NAME,
    EMPLOYEE_DATAVAL.DEPTNO,
    EMPLOYEE_DATAVAL.EMPNAME,
    EMPLOYEE_DATAVAL.EMPNO,
    EMPLOYEE_DATAVAL.SALARY
));
Table<?> t = EMPLOYEE
   .join(EMPLOYEE_DATAVAL)
     .on(EMPLOYEE.DEPT_ID.eq(EMPLOYEE_DATAVAL.DEPTNO)
     .or(EMPLOYEE_DATAVAL.EMPNAME.eq(EMPLOYEE.EMP_NAME));

if (someCondition) {
    t = t.leftJoin(EMP_SAL).on(EMPLOYEE.EMP_ID.eq(EMP_SAL.EMP_ID));
    c.addAll(Arrays.asList(
        EMP_SAL.EMP_ID,
        EMP_SAL.SALARY
    ));
}

Result<?> result =
ctx.select(c)
   .from(t)
   .fetch();

Answer 2

In first case error is coming because of the wrong ) in wrong place as pointed below; Which making the end of query and so LEFT JOIN throwing an error. the ) must be at end of the query.

FROM Employee
INNER JOIN Employee_DataVal
ON Employee.Dept_ID = Employee_DataVal.DeptNo
OR Employee_DataVal.EmpName = Employee.Emp_Name ) <--Here
LEFT JOIN Emp_Sal

In second case, you are trying to do a self join to the same table in that case as the error already suggested you, you need to use correlation names like

FROM Employee emp1 <-- Here used a table alias emp1
INNER JOIN Employee_DataVal ed
ON emp1.Emp_ID = ed.EmpNo
AND emp.Dept_ID = ed.DeptNo
LEFT JOIN Employee emp2 <-- Here used a different table alias emp2
ON ed.EmpName = emp2.Emp_Name

Moreover, the LEFT JOIN Employee won't make any sense here and which can simply be modified to below code

FROM Employee emp1 
INNER JOIN Employee_DataVal ed
ON emp1.Emp_ID = ed.EmpNo
AND emp.Dept_ID = ed.DeptNo
AND emp.Emp_Name = ed.EmpName <-- here by adding another join condition

Answer 3

You are using LEFT Join same as the Self join. which is actually creating the problem.

Answer 4

Speaking directly to the syntax errors:

1. The parentheses in this statement are invalid. Removing them will solve the problem.

2. The table Employee is used twice in the FROM clause. You must alias the tables for this to work.

   Select dbo.Employee.Dept_ID,dbo.Employee.Emp_ID, dbo.Employee.Emp_Name,dbo.Employee_DataVal.DeptNo, dbo.Employee_DataVal.EmpName,dbo.Employee_DataVal.EmpNo ,dbo.Emp_Sal.Emp_ID,dbo.Emp_Sal.Salary FROM Employee e1 INNER JOIN Employee_DataVal ON e1.Emp_ID = Employee_DataVal.EmpNo AND e1.Dept_ID = Employee_DataVal.DeptNo LEFT JOIN Employee e2 ON Employee_DataVal.EmpName = e2.Emp_Name

Speaking to your broader question, the concept of a generic SQL query generator is quite common and has had several implementation. You won't find full implementation guidance in a forum such as this.

Cheers!