Inner-Outer joins in WHERE clause error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inner-Outer joins in WHERE clause error

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2012-02-26 : 20:36:39

Good evening, ive got this query and works fine without the WHERE clause, bur when i insert the where clause into the query yields an error, the code is this:

set nocount on
select cl.nom_cliente,cl.cod_cliente,cl.dir_cliente,ha.COD_ABONADO,ha.num_celular,hmp.valor_ultimo_recargo,
pt.cod_plantarif,pt.des_plantarif
from clientes cl
inner join hechos_abonados ha
on cl.cod_cliente=ha.cod_cliente
WHERE ha.num_celular = 3154731424
left outer join Plan_Tarifario pt
on ha.cod_plantarif=pt.cod_plantarif
left outer join hechos_movto_prepago hmp
on pt.cod_plantarif=hmp.cod_plantarif

The error that yiled is:

Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'left'

Id appreciate your help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-26 : 21:22:44

You should do all the joins in the FROM clause. The way you have written it, the joins are in the WHERE clause - you cannot have that. The following will fix the syntax problem - but I don't know whether that is implementing the logic you are trying to implement:

SET NOCOUNT ON
SELECT cl.nom_cliente,
       cl.cod_cliente,
       cl.dir_cliente,
       ha.COD_ABONADO,
       ha.num_celular,
       hmp.valor_ultimo_recargo,
       pt.cod_plantarif,
       pt.des_plantarif
FROM   clientes cl
       INNER JOIN hechos_abonados ha
            ON  cl.cod_cliente = ha.cod_cliente
       LEFT OUTER JOIN Plan_Tarifario pt
       ON ha.cod_plantarif = pt.cod_plantarif
       LEFT OUTER JOIN hechos_movto_prepago hmp
       ON pt.cod_plantarif = hmp.cod_plantarif
WHERE  ha.num_celular = 3154731424

alejo46
Posting Yak Master

157 Posts

Posted - 2012-02-27 : 16:27:08

OK, thank yor very much, but what ive intended is not to use the WHERE clause for all joins, its just fot the first join betwwen client and hechos abonados tables, because hechos_abonados table is the one i want to filter for the num_celular field so, dont know if parenthesis are missing or something like that.

Id appreciate your help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-27 : 17:32:07

quote:
Originally posted by alejo46

OK, thank yor very much, but what ive intended is not to use the WHERE clause for all joins, its just fot the first join betwwen client and hechos abonados tables, because hechos_abonados table is the one i want to filter for the num_celular field so, dont know if parenthesis are missing or something like that.

Id appreciate your help

The way I wrote it, it will filter only on num_celular column of hechos_abonados. The result will be left joined with the rest of the query. If that is not doing what you are expecting it to do, can you post some sample input data and the corresponding output you are expecting to see?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources