I have a query which is fetching values from multiple tables, it looks some thing close to the below query.
I have highlighted in bold the place where i need to pass the value returned from the same query.
select E.[EmployeeName] as EmployeeName
, (select City from tblEmployeeCities where C.EmployeeID = E.EmployeeID) as EmployeeCity
, (Select State from tblStates **where City = /i need to give the name of
the city returned by the above statement** ) as EmployeeState
from tblEmployees E
Use JOIN/LEFT JOIN:
SELECT E.EmployeeName, C.City, S.State
FROM tblEmployees E
LEFT JOIN tblEmployeeCities C ON C.EmployeeID=E.EmployeeID
LEFT JOIN tblStates S ON S.City=C.City
WHERE in the first JOIN with ON.Maybe something like
select E.[EmployeeName] as EmployeeName
, c.City as EmployeeCity
, (Select State from tblStates where City = c.City ) as EmployeeState
from tblEmployees E
inner join tblEmployeeCities C on C.EmployeeID = E.EmployeeID
I think this can be achieved with a CASE WHEN ELSE END statment in the select part of your sql statment.
Look at example here for syntax or usage: https://www.w3schools.com/sql/func_mysql_case.asp
