SQL syntax- two tables, Join?

[scudsucker]

Hi-
I have two tables in a db on a SQL server. Lets name them tblCompanies and tblDirectors.

The structure can be simplified to

tblCompanies: intCompanyID (int IDENTITY) and vchCompanyName (varChar)

and

tblDirectors: intDirectorID (int IDENTITY) and intCompanyID (int) and vchDirectorName (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?

I'm curently using this:

SELECT DISTINCT 
tblCompanies.intCompanyID, tblCompanies.vchCompanyName,
tblDirectors.intDirectorID, tblDirectors.vchDirectorName, 
FROM tblCompanies, tblDirectors
WHERE 	
tblCompanies.intCompanyID=tblDirectors.intDirectorID

But if, say, there are 3 directors per company, the recordset repeats the company ID and name 3 times each.

Any suggestions? Ive tried INNER and OUTER, LEFT and RIGHT JOINS, but cannot get the required result.

[ZorTiger]

have you tryed something like:

SELECT DISTINCT
tblCompanies.intCompanyID, tblCompanies.vchCompanyName,
tblDirectors.intDirectorID, tblDirectors.vchDirectorName,
FROM tblCompanies LEFT JOIN tblDirectors ON tblCompanies.intCompanyID=tblDirectors.intDirector ID

[ZorTiger]

when there are more then 1 director, it is above my SQL ablitys, more over, i think its not a limit to mySQL, its a limit to PHP

so your best is to do 2 actual querys..

Iv crafted SQL thats done what you want do .. in things like Access, but i cant get it to work with php/mysql

Its possable this is fixed in php5 with mysql4, there is a hole new more advanced exention, and a handful of new commands in mysql4 as well.

[Musicman]

Hi,

since mysql does not support the
select distinct on (...)
clause, your option would be to loop over all results in php and show just the first one for each company

like
$cid = 0;
while($row = mysql_fetch_row($result))
{ if($cid != $row[0])
{ // print result here, then
$cid = $row[0];
}
}

Musicman

[ZorTiger]

actually, i came up with it dose allow select distinct

if you search on mysql' site for SELECT Syntax

you will come up with this:
http://dev.mysql.com/doc/mysql/en/SELECT.html

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name']
[FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
[ASC | DESC] , ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]


How ever this could be doc for version 4. It seems like Mysql's featured tripled in it.