Hi-
I have two tables in a db on a SQL server. Lets name them tblCompanies and tblDirectors.
The structure can be simplified to
andCode:tblCompanies: intCompanyID (int IDENTITY) and vchCompanyName (varChar)
How do I write a SQL statement that will return a list of the companies, and only the first DirectorName that has a corresponding companyID?Code:tblDirectors: intDirectorID (int IDENTITY) and intCompanyID (int) and vchDirectorName (varchar)
I'm curently using this:
But if, say, there are 3 directors per company, the recordset repeats the company ID and name 3 times each.Code:SELECT DISTINCT tblCompanies.intCompanyID, tblCompanies.vchCompanyName, tblDirectors.intDirectorID, tblDirectors.vchDirectorName, FROM tblCompanies, tblDirectors WHERE tblCompanies.intCompanyID=tblDirectors.intDirectorID
Any suggestions? Ive tried INNER and OUTER, LEFT and RIGHT JOINS, but cannot get the required result.




Reply With Quote