i see two opening brackets and one closing bracket..

Welcome aboard tejayuu, a couple things. first, you are missing a )

SELECT t1.* , t2.name AS repliername, t3.centre_name
FROM ((tickets AS t1 LEFT JOIN users AS t2 ON t1.replierid = t2.id)
LEFT JOIN centres as t3 ON t3.id = t1.name )

which shouldn't be necessary anyway.

SELECT t1.* , t2.name AS repliername, t3.centre_name
FROM tickets AS t1 LEFT JOIN users AS t2 ON t1.replierid = t2.id
LEFT JOIN centres as t3 ON t3.id = t1.name

Second, I have never done "table as [placeholder]," I think those only apply to selected fields. Try


SELECT tickets.* , users.name AS repliername, centers.centre_name
FROM tickets LEFT JOIN users ON tickets.replierid = users.id
LEFT JOIN centres ON centres.id = tickets.name

A thing to be aware of, left joins will return rows even if no matching fields are found for the joined tables. For example, if there's no matching record in centres,

ticketfield1|ticketfield2|repliername|NULL

If you want to display only rows with matching records in all three, this is a bit more legible and will do that.

select tickets.*, users.name as repliername, centers.centre_name
from tickets,users,centres
where tickets.replierid = users.id
and tickets.name = centres.id

If there are records in all three, the results *should* be the same.

Thanks all for the input. The work I am doing is at a different site and as it's development it's stored locally and I can't access it. I'll try these tomorrow on my return, but thanks anyway.

rocknbil

Second, I have never done "table as [placeholder]," I think those only apply to selected fields.

I really can't comment on this as I'm not heavily into MySQL or PHP. I am trying to modify an Open Source application (HESK) to suit a need we have and this query is lifted from that app (it also uses `backticks` but I've taken those out).

Thinking back to dawn of time (my college days - 1990 and a typing error) I seem to remember using "table as [placeholder]," if you were trying to join the same table to itself (can't remember why we were doing that) although things have probably changed since then (before the Internet). Anyway big thanks for your help - I'll be able to try tomorrow.

Cheers
Tony

Well then, it's probably fine using table as r (or whatever,) I've just never used it. Generally you use placeholders or variables (forget the real name) for use of access. For example,

select date_sub(curdate(), interval 7 day);

gives you

------------------------------------- 
|date_sub(curdate(), interval 7 day)| 
------------------------------------- 
|2010-08-25.............................| 
------------------------------------- 

And vla PHP, you can only access it by indexed array, not named field:
while ($row=mysql_fetch_array($result)) {
echo $row[0];
// name? There isn't one . . .
}

but this
select date_sub(curdate(), interval 7 day) as last_week;

gives you

----------- 
|last_week| 
----------- 
|2010-08-25| 
----------- 

and you can now do this.

while ($row=mysql_fetch_array($result)) {
echo $row[0];
echo $row['last_week']; // Same thing! :-)
}

This is why I don't see a need for "as" on a table name, but it's a trivial point.

(it also uses `backticks` but I've taken those out).

Backtics are recommended but only to insure none of your table names or field names conflict with reserved words. For example, date and datetime are mySQL field types, if you somehow manage to create a field named date this will error,

select date from table;

where this will not.

select `date` from table;

Hi rocknbil

Got in this morning and put the closing parenthesis back in and it all worked.

Have seen different arguements about backticks but now I know what they are for I will continue to use them.

I understand that placeholders are mainly used to "clean up" field names and such, but was just pointing out that I had a vague recollection on using them for tables didn't mean to create an arguement.

Thanks again
Tony

We don't argue here, we just explore. :-)