join problem

[Sjoerd]

Hi,

I have a problem with mysql joins, which are always a bit complicated
to me.
I use mysql version 4.0.25-standard.

This is my SQLstatement, used to check if user #1 has the right to
login:

SELECT users.*, rights.* FROM users
LEFT JOIN join_users_rights ON users.id=join_users_rights.user
LEFT JOIN rights ON join_users_rights.right=rights.id
WHERE ((rights.name='login') AND (users.id='1'))

And this is the error message I get:
#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
'right = rights . id WHERE ( ( rights . name = 'login' ) AND ( u

Where do I go wrong?

Thanks in advance,

Sjoerd Mulder

[Aggro]

Sjoerd wrote:

'right = rights . id WHERE ( ( rights . name = 'login' ) AND ( u

See the "rights . id"? That should propably be rights.id. Same for
"rights . name".

[Rich Ryan]

It would be very nice if you would post your CREATE TABLE statement, along
with some INSERT INTOs. That way we could post a solution that is tested.

Rich
"Sjoerd" <go****@sjoerdmulder.electronischepost.nl> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Hi,

I have a problem with mysql joins, which are always a bit complicated
to me.
I use mysql version 4.0.25-standard.

This is my SQLstatement, used to check if user #1 has the right to
login:

SELECT users.*, rights.* FROM users
LEFT JOIN join_users_rights ON users.id=join_users_rights.user
LEFT JOIN rights ON join_users_rights.right=rights.id
WHERE ((rights.name='login') AND (users.id='1'))

And this is the error message I get:
#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
'right = rights . id WHERE ( ( rights . name = 'login' ) AND ( u

Where do I go wrong?

Thanks in advance,

Sjoerd Mulder

[Sjoerd]

CREATE TABLE `join_users_rights` (
`id` int(11) NOT NULL auto_increment,
`user` int(11) NOT NULL default '0',
`right` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

CREATE TABLE `rights` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(30) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(30) NOT NULL default '',
`password` varchar(32) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

INSERT INTO `rights` VALUES (1, 'login', 'Login', 1, 1);
INSERT INTO `users` VALUES (1, 'sjoerd', 'mypassword');
INSERT INTO `join_users_rights` VALUES (1, 1, 1);

Thanks in advance,

Sjoerd Mulder

Rich Ryan wrote:

It would be very nice if you would post your CREATE TABLE statement, along
with some INSERT INTOs. That way we could post a solution that is tested.

Rich
"Sjoerd" <go****@sjoerdmulder.electronischepost.nl> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Hi,

I have a problem with mysql joins, which are always a bit complicated
to me.
I use mysql version 4.0.25-standard.

This is my SQLstatement, used to check if user #1 has the right to
login:

SELECT users.*, rights.* FROM users
LEFT JOIN join_users_rights ON users.id=join_users_rights.user
LEFT JOIN rights ON join_users_rights.right=rights.id
WHERE ((rights.name='login') AND (users.id='1'))

And this is the error message I get:
#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
'right = rights . id WHERE ( ( rights . name = 'login' ) AND ( u

Where do I go wrong?

Thanks in advance,

Sjoerd Mulder

[Sjoerd]

No that's not it. See my own statement. Those spaces appear only in the
error message Mysql gives. But thanks.

[Sjoerd]

So what I want to do, is to check if there exists a user #1 with the
right to 'login'. Another solution will also be fine, although I would
like to understand why my query is wrong.

[Aggro]

Sjoerd wrote:

CREATE TABLE `rights` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(30) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ; INSERT INTO `rights` VALUES (1, 'login', 'Login', 1, 1);

The insert doesn't work. There is 3 columns in the table, but insert has
5 columns.

# But if we replace that with:
INSERT INTO `rights` VALUES (1, 'login', 'Login');

# Then we can execute the query you gave in the first message.
# No errors:
mysql> SELECT users.*, rights.* FROM users
-> LEFT JOIN join_users_rights ON users.id=join_users_rights.user
-> LEFT JOIN rights ON join_users_rights.right=rights.id
-> WHERE ((rights.name='login') AND (users.id='1'));
+----+--------+------------+----+-------+-------------+
| id | name | password | id | name | description |
+----+--------+------------+----+-------+-------------+
| 1 | sjoerd | mypassword | 1 | login | Login |
+----+--------+------------+----+-------+-------------+
1 row in set (0.00 sec)

But error messages should not add spaces where you claimed it was added.
I suspect that something changes your query and adds those spaces to it.

[Sjoerd]

You were right about the insert. I gave the wrong create statement:

CREATE TABLE `rights` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(30) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`defaultvalue` tinyint(1) NOT NULL default '0',
`order` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

The query does give errors though. The error message appears when I
execute the command either in PHP or in PHPMyAdmin.

Could it have to do with a specific MySQL version? A bug? I use mysql
version 4.0.25-standard.

[Aggro]

Sjoerd wrote:

You were right about the insert. I gave the wrong create statement:
Tested with new one, works ok with that also.
Could it have to do with a specific MySQL version? A bug? I use mysql
version 4.0.25-standard.

Possible, but unlikely

I have:
Server version: 4.0.24_Debian-10ubuntu2-log

Where do you execute this query? Do you use the MySQL command line tool
for it? If not, try it.

[Sjoerd]

Like I said, via PHP or PHPMyAdmin. I have no access to any command
line tool, unfortunately.

I tested the query on another server on which I host a page: it worked.
I think I will try to work around it with several querys and combine
them in PHP.

Thanks for your help.

[Sjoerd]

I found the problem!
I renamed every instance of 'right' to something else, and then it
worked. It seems that it is a 'reserved' term for mysql, although not
in every version.

Thanks for all your help.