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_righ ts ON users.id=join_u sers_rights.use r
LEFT JOIN rights ON join_users_righ ts.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]

Re: join problem

Sjoerd wrote:
[color=blue]
> 'right = rights . id WHERE ( ( rights . name = 'login' ) AND ( u[/color]

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

[Rich Ryan]

Re: join problem

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" <google@sjoerdm ulder.electroni schepost.nl> wrote in message
news:1135121224 .447892.212280@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> 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_righ ts ON users.id=join_u sers_rights.use r
> LEFT JOIN rights ON join_users_righ ts.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
>[/color]

[Sjoerd]

Re: join problem

CREATE TABLE `join_users_rig hts` (
`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_rig hts` VALUES (1, 1, 1);

Thanks in advance,

Sjoerd Mulder

Rich Ryan wrote:[color=blue]
> 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" <google@sjoerdm ulder.electroni schepost.nl> wrote in message
> news:1135121224 .447892.212280@ g44g2000cwa.goo glegroups.com.. .[color=green]
> > 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_righ ts ON users.id=join_u sers_rights.use r
> > LEFT JOIN rights ON join_users_righ ts.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
> >[/color][/color]

[Sjoerd]

Re: join problem

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

[Sjoerd]

Re: join problem

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]

Re: join problem

Sjoerd wrote:
[color=blue]
> 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 ;[/color]
[color=blue]
> INSERT INTO `rights` VALUES (1, 'login', 'Login', 1, 1);[/color]

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_righ ts ON users.id=join_u sers_rights.use r
-> LEFT JOIN rights ON join_users_righ ts.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]

Re: join problem

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]

Re: join problem

Sjoerd wrote:[color=blue]
> You were right about the insert. I gave the wrong create statement:[/color]

Tested with new one, works ok with that also.
[color=blue]
> Could it have to do with a specific MySQL version? A bug? I use mysql
> version 4.0.25-standard.[/color]

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]

Re: join problem

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]

Re: join problem

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.