Hi to everyone here - This is my first post/question!
I have a problem in which I need to construct (what appears to be) a complex Select Statement. For brevity I list the data construct and the problem I need to solve.
My Data Strucutre:
JobSheet_Master.cbo
------------------------
JobId as Integer
ClientID as Integer
ConsultantID as Integer
ReviewConsultantID as Integer (***This call to lookup table is the problem)
...
Client_Master.cbo
------------------------
ClientID as Integer
ClientName as String
...
Consultant_Master.cbo
------------------------
ConsultantID as Integer
ConsultantName as String
...
My problem is that both the ConsultantID and ReviewConsultantID fields in the JobSheet_Master table need to lookup the Consultant_Master table to get the ConsultantName and ReviewConsultantName (which are different but both identified by the field ConsultantID in the Consultant_Master table. I have devised a temporary work around whereby I get the data I need from the select table and then do a separate datalookup to get the ReviewConsultant. I know this is inefficeint but cannot see how to construct a Select Statement that will achieve the results. Whilst I have only shown one example in this table, I actually have three instances of this that I need to solve, hence my query to this forum (which I hope is the right one!!)
The (abbreviated) SQL code I currently use looks like this:
Code:
SELECT JobSheet_Master.JobID, _
JobSheet_Master.ClientID, _
Client_Master.ClientName As ClientName _
JobSheet_Master.ConsultantID, _
Consultant_Master.ConsultantName As ConsultantName _
JobSheet_Master.ReviewConsultantID _
FROM (JobSheet_Master INNER JOIN ConsultantID ON _(JobSheet_Master.ConsultantID=Consultant_Master.ConsultantID)) _
INNER JOIN ClientID ON (JobSheet_Master.ClientID=Client_Master.ClientID) _
ORDER BY JobSheet_Master.JobID "
Loading the results into a structure, I then do a separate SQL SELECT to get the Review Consultant Name.
Any help with constructing a SELECT clause that could get me both the ConsultantName and ReviewConsultantName from the Consultant_Master table would be very much appreciated.
Help with Complex SQL Select Statement
