MySQL SELECT of mutiple tables

charlieb

I am using MySQL and attempting to select from two tables. Some of the columns in the tables have the same name however I am only recieving the data for the column in the first table.

My SQL code is:
$query_Service = sprintf("SELECT * FROM service, company WHERE service.DQNum = %s and company.CompRef = service.CompRef", $SlctDQNum_Service);

$Service = mysql_query($query_Service, $dbasename) or die(mysql_error());

$row_Service = mysql_fetch_assoc($Service);

After this $row_Service contains the columns from service and columns from company where the column name is not also in service, but not columns from company that have the same name as a column in service.

Am I missing something, or is this a bug?

Charlie B

Oersoep

Try adding the table names to your query:

SELECT service., company. FROM service, company WHERE service.DQNum = %s and company.CompRef = service.CompRef

Than your result will contain both service.something and company.something

charlieb

Oersoep, thanks for the reply.

I've tried what you have suggested and have the same problem as before. When I look at the array $row_Service it only contains the simple names of the columns which is, I suppose, why it doesn't give me columns from both tables with the same name.

Any other suggestions?

Oersoep

Call the fields by name and/or give them an alias.

SELECT service.john as s_john, company.john as c_john FROM.....

charlieb

Thanks.

This does work, but it's a bit of a ball ache. Is this really the easiest way?

In this case I can just use two separate selects, one for each table, which is much quicker to code.