Join help

[Andy-H]

 

Hi, I have the following query using multiple joins, I'm not that used to using joins and couldn't even say if the syntax is correct, would appreciate it if anyone can help me resolve this problem.

 

 

The query is:

"SELECT co.title, co.f_name, co.l_name, cu.reference, cu.created_by_id, cu.created_date, " . 
                   "p.addr1, p.addr2, p.town, p.city, p.county, p.postcode, p.country_id, " .
                   "p.long, p.lat, p.primary, ci.info, t.type " .
                   "FROM " . 
                   "customer cu, " .
                   "INNER JOIN ( contacts AS co, premesis AS p, contact_info AS ci, types AS t ) " . 
                   "ON ( co.customer_id = cu.customer_id " .
                   "AND p.customer_id  = cu.customer_id " . 
                   "AND ci.contact_id = co.contact_id " . 
                   "AND t.type_id = ci.type_id ) " . 
                   "WHERE cu.customer_id = $cust_id LIMIT 1"

 

 

And the error is:

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 'INNER JOIN ( contacts AS co, premesis AS p, contact_info AS ci, types AS t ) ON ' at line 1

[mikosiko]

First... you should read a little more about JOIN's syntax ... here...

now... as an example, here is what apparently you were trying to write:

"SELECT co.title, 
       co.f_name, 
       co.l_name, 
       cu.reference, 
       cu.created_by_id, 
       cu.created_date,
       p.addr1, 
       p.addr2, 
       p.town, 
       p.city, 
       p.county, 
       p.postcode, 
       p.country_id,
       p.`long`, 
       p.lat, 
       p.`primary`, 
       ci.info, 
       t.`type`
  FROM customer cu,
    JOIN contacts AS co ON co.customer_id = cu.customer_id 
    JOIN contact_info AS ci ON ci.contact_id = co.contact_id
    JOIN premesis AS p ON p.customer_id  = cu.customer_id 
    JOIN `type` AS t ON t.type_id = ci.type_id
  WHERE cu.customer_id = $cust_id LIMIT 1"

 

Some suggestions:

- Don't use unnecessary "

- Notice how I did enclose some fields/table names in backtics (`) that was neccesary because you are using MYSQl reserved words, the best option, if that still possible, is to change the fields/table names to something that is not a reserved word, otherwise you must use the backtics every time in your application for those fields/tables.

 

[Andy-H]

Sorry mate it was the comma after the customers cu, it shouldn't have been there. I didn't need to wrap any field names in backticks.

 

 

Thanks for the help.

[mikosiko]

Humm... interesting.... what Mysql version are you using?.... asking because at least starting with version 5.0 the words "long", and "primary" are reserved words  (the word "type" is not.. that was my mistake)... good to know that it works for you.

 

and BTW: my apologizes for the reference to the JOIN syntax... I'm so used to the long version that I forgot completely about the short one.

[Andy-H]

Not sure to be honest mate, its on works server, probably v4, hasn't been updated in a while.

 

 

Thats fine mate, I'm not all that when it comes to joins so I appreciate the help.

 

 

On another note, I made a suggestion on the mysql forum:

http://forums.mysql.com/read.php?132,419652,419652#msg-419652

 

 

If anyone wants to check it out and give it a 'push' in the right direction if its possible and you think it's a good idea?

[mikosiko]

it is ok... I found the reason reading the "small prints" in the manual

Reserved words are permitted as identifiers if you quote them as described in Section 8.2, “Schema Object Names{/b]”:

 

mysql> CREATE TABLE interval (begin INT, end INT);

ERROR 1064 (42000): You have an error in your SQL syntax ...

near 'interval (begin INT, end INT)'

 

mysql> CREATE TABLE `interval` (begin INT, end INT);

Query OK, 0 rows affected (0.01 sec)

Exception: A word that follows a period in a qualified name must be an identifier, so it need not be quoted even if it is reserved

[Andy-H]

Ahh thats news to me too, cheers