SQL query syntax

[Iain]

Can I just check I have some of this right? It's fairly basic, but just want to check I'm setting about it the right way, as I'm new to the coding. (have in the past used Access a lot).

Basically I have tables for companies and contacts - in a one to many relationship, as any given company may have many contacts - which as far as I can tell is how you should do this rather than have a single contacts table? (normalisation?)

So if the tables are :

Companies :
CompanyID (INT, auto increment)
Company
Address
etc

Contacts :
ContactID (INT, auto increment)
FirstName
LastName
etc

CompanyContacts :
CompanyID (INT)
ContactID (INT)

what should the SQL be for the page to display a company's details along with each contact ?

I thought it might be something fairly simple like :

SELECT *
FROM Companies, CompanyContacts, Contacts
WHERE Company.CompanyID=CompanyContacts.CompanyID

But the Access flavour query comes out as :

SELECT *
FROM ([Companies] INNER JOIN [CompanyContacts] ON [Companies].CompanyID = [CompanyContacts].CompanyID) INNER JOIN [Contacts] ON [CompanyContacts].ContactID = [Contactas].ContactID;

If someone could translate the Access flavour query into PHP/mySQL flavour that I could use as a reference that would be a great help.

Cheers,
Iain

[Piranha]

First you don't have to have the table in between since it's a one-to-many relation. You should do something like the following:

Companies :
CompanyID (INT, auto increment)
Company
Address
etc

Contacts :
ContactID (INT, auto increment)
CompanyID (INT, FK to Company(CompanyID))
FirstName
LastName
etc

do what you want you should use something simular to the following:

SELECT *
FROM Companies, Contacts
WHERE Companies.CompanyID=Contacts.CompanyID

You could use inner join as well, but it's easier to join tables using the where-command. And it basically does the exact same thing.