Welcome to WebmasterWorld Guest from 54.167.155.147

Forum Moderators: open

Message Too Old, No Replies

Joining 3 tables

   
5:35 pm on Aug 31, 2010 (gmt 0)



Hi all

I'm just trying to write a query in MySQL that joins 3 tables and am getting the following error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4


from the following query

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


Any ideas as I can't find '' in my query
5:46 pm on Aug 31, 2010 (gmt 0)



i see two opening brackets and one closing bracket..
5:55 pm on Aug 31, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
9:10 am on Sep 1, 2010 (gmt 0)



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
4:22 pm on Sep 1, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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;
7:04 am on Sep 2, 2010 (gmt 0)



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
4:44 pm on Sep 2, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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