Query syntax error selecting from 3 tables

Question

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 groups WHERE  id = '.$this->id; /*objectid*/
    foreach($result2 as $extra){
        $info['groupname'] = $extra['name'];
    }
    return $info;
}

could you also post the whole execution bloc please? I'ld like to see which one is line 3 — Sebas, May 31 '12 at 01:34
this was the whole line: WHERE usuarios.id = '.$this->id_user.' AND groups.id = '.$this->id_group.' AND locs.id = '.$this->id_loc.' LIMIT 1'; — Toni Michel Caubet, May 31 '12 at 01:37
this is indeed pretty weird. What if you totally remove this line? no where clause? — Sebas, May 31 '12 at 01:38
Edited the question with the php code, please note the table names may change a bit (its in spany some of it) — Toni Michel Caubet, May 31 '12 at 01:39
ok, thanks! But could you please try to just remove the where clause to see if at least it compiles? I want to be sure your encoding of carriage return is correct — Sebas, May 31 '12 at 01:41
oh i see now, there was a typo in one of the fields.. thanks for the tip ;) its a bit frustrating the 'near to' erro, hehe — Toni Michel Caubet, May 31 '12 at 01:45
@ToniMichelCaubet - You mean, where clause still failing to compile? — Ravinder Reddy, May 31 '12 at 02:12
@ToniMichelCaubet Can you please post your query after concatenation — Ravinder Reddy, May 31 '12 at 02:13
i edited with a print before execution and with php generator — Toni Michel Caubet, May 31 '12 at 02:30

score 0 · Answer 1 · answered May 31 '12 at 02:13

0

What about this?

$sql = 'SELECT usuarios.name as username,usuarios.avatar as useravatar, usuarios.id_loc, locs.name as locname';
$sql .= ' FROM usuarios, groups, locs';
$sql .= ' WHERE usuarios.id = '.$this->id_user.' AND groups.id = '.$this->id_group.' AND locs.id = '.$this->id_loc.' LIMIT 1';

$res = mysql_query($sql) or die(mysql_error());

answered May 31 '12 at 02:13

Sebas

21,192
9
55
109

same query different concatenation? – Toni Michel Caubet May 31 '12 at 02:28
exactly, this way you skip the annoyment of chr(13) misinterpreted. Just wondering... – Sebas May 31 '12 at 02:29
thanks for your help but the problem seems to be in the query – Toni Michel Caubet May 31 '12 at 02:30

King Skippus · Answer 2 · 2012-05-31T04:40:11.830

You need something to relate usuarios or locs to groups. I don't know your schema, but it will probably be something 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
    [something?] = groups.id AND    -- This is what you're missing.
                                    -- What table/column relates to groups?
    usuarios.id = 1 AND
    group.id = 1
LIMIT 1

...where [something?] is probably an id_group column in either usuarios or locs. Right now, your top query has two errors. The first is a simple syntax error, you don't specify a group.id value. The second one, though, which is more serious, is that you don't have a join clause for your groups table.

If you post your schemas for the three tables, I can probably give you an even better answer, but I'm betting that's what you're missing. If you don't have a group_id column in usuarios or locs, then you need to be more clear about what exactly you're looking to query, and some example data might be helpful with what your expected result is.

score 0 · Accepted Answer · answered May 31 '12 at 16:42

well after some help friend i got to this solution

SELECT usuarios.first_name,usuarios.last_name,usuarios.avatar,poblacion.poblacion,groups.name as groupname
                                FROM usuarios LEFT JOIN poblacion ON usuarios.id_loc=poblacion.idpoblacion,
                                     helps LEFT JOIN groups ON helps.id_group = groups.id
                                WHERE  usuarios.id = '.$this->id_user.' AND groups.id = '.$this->id_group

Query syntax error selecting from 3 tables

3 Answers3