homepage Welcome to WebmasterWorld Guest from 184.72.82.126
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Joining 3 tables
teejayuu




msg:4194662
 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

 

bhukkel




msg:4194665
 5:46 pm on Aug 31, 2010 (gmt 0)

i see two opening brackets and one closing bracket..

rocknbil




msg:4194667
 5:55 pm on Aug 31, 2010 (gmt 0)

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.

teejayuu




msg:4194907
 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

rocknbil




msg:4195115
 4:22 pm on Sep 1, 2010 (gmt 0)

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;

teejayuu




msg:4195442
 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

rocknbil




msg:4195654
 4:44 pm on Sep 2, 2010 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved