joining 3 tables in 1 query

bhphpprogrammer

From MS Access 2000 I have this SQL

SELECT DISTINCT mycontacts.firstname, mycontacts.lastname, mycontacts.city, mycontacts.state, clients.ClientID, clients.Company, clients.MainPhone, clients.city, clients.state
FROM (contactlookup INNER JOIN mycontacts ON contactlookup.CID = mycontacts.CID) INNER JOIN clients ON contactlookup.ClientID = myclients.ClientID
WHERE (((mycontacts.firstname) Like 'ma') AND ((mycontacts.lastname) Like 'anderson') AND ((myclients.ClientID)>0))
ORDER BY mycontacts.firstname, mycontacts.lastname, myclients.ClientID DESC;

I need to change the format to fit MYSQL and can't find any examples to do it.

Thank you for your help.

Roger_Ramjet

Almost identical - in fact I'd have thought that it would run in mysql as is

"SELECT DISTINCT mycontacts.firstname, mycontacts.lastname, mycontacts.city, mycontacts.state, clients.ClientID, clients.Company, clients.MainPhone, clients.city, clients.state
FROM contactlookup INNER JOIN mycontacts ON(contactlookup.CID = mycontacts.CID) 
INNER JOIN myclients ON(contactlookup.ClientID = myclients.ClientID)
WHERE ((mycontacts.firstname Like '*ma*') AND (mycontacts.lastname Like '*anderson*') AND (myclients.ClientID>0))
ORDER BY mycontacts.firstname, mycontacts.lastname, myclients.ClientID DESC";

I did notice that you have the name 'clients' rather than 'myclients' in the second join - so it was probably just that typo that was the problem.

Access does like to scatter brackets around and they are not necessary in either access or mysql.

fasmy98

I would like to join multiple tables, but when I tried it and error appeared exceed maximum.
Any help please?

fasmy98

SELECT jawatan.kod_jawatan, jawatan.kod_gaji, jawatan.kod_skim, jawatan.jawatan,
b.ID_B, b.post, b.bil_06, b.bil_07,b.keterangan,
t.ID_A,
c.id_bhgn,c.bahagian
FROM tabletest AS t JOIN bil_jawatan AS b ON t.ID_A=b.ID_B
JOIN bahagian AS c ON t.bahagian=c.id_bhgn
JOIN jawatan ON t.kod_jawatan=jawatan.kod_jawatan
WHERE t.bahagian='$search1'")

Roger_Ramjet

maximum what? execution time?

fasmy98

Roger Ramjet wrote:
maximum what? execution time?

yes, it appeared the execution time exceed 30 second and my system will hang.

is it because of the query?

Roger_Ramjet

No it is because of the max execution time in your server settings