Query syntax error selecting from 3 tables

Posted by Toni Michel Caubet on Stack Overflow See other posts from Stack Overflow or by Toni Michel Caubet
Published on 2012-05-31T01:31:42Z Indexed on 2012/05/31 4:40 UTC
Read the original article Hit count: 159

Filed under:
|
|

Given info about an object: id, user_id, group_id

Given info about an user: id_user, id_loc

I need to get i one query:

  • The name of the user (in table users)

  • The name of the location of the user (in table locs)

  • The name of the group of the object (in table groups)

I am trying like this:

SELECT usuarios.first_name as username, usuarios.id as userid, usuarios.avatar as useravatar, usuarios.id_loc, locs.name as locname, groups.name as groupname FROM usuarios,groups,locs WHRE usuarios.id_loc = locs.id AND usuarios.id = 1 AND group.id = LIMIT 1

having an error saying

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 'AND locs.id = 3 LIMIT 1' at line 3

What am i doing wrong? can i do this in one query?

-EDIT-

This is the query generator code (php+mysql):

$query_loc_group_user = 'SELECT usuarios.first_name as username,
                                            usuarios.id as userid,
                                            usuarios.avatar as useravatar,
                                            usuarios.id_loc, 
                                            locs.name as locname,
                                            groups.name as groupname
                                     FROM   usuarios,groups,locs
                                     WHRE   usuarios.id_loc = locs.id
                                     AND    usuarios.id = '.$this->id_user.'
                                     AND    group.id = '.$this->id_group.'
                                     LIMIT 1';

In case it helps, i am trying to do in one query this

function get_info(){
    $info;
    $result = cache_query('SELECT first_name,last_name,avatar FROM   usuarios WHERE  id = '.$this->id_user);
    foreach($result as $extra){
        $info['username'] = $extra['first_name'].' '.$extra['last_name'];
        $info['avatar'] = $extra['avatar'];
    }
    $result1 = cache_query('SELECT name FROM locs WHERE  id = '.$this->id_user);
    foreach($result1 as $extra){
        $info['locname'] = $extra['name'];
    }   
    $result2 = cache_query('SELECT name FROM locs WHERE  id = '.$this->id_user);
    foreach($result2 as $extra){
        $info['groupname'] = $extra['name'];
    }
    return $info;
}

© Stack Overflow or respective owner

Related posts about mysql

Related posts about sql