Solved

Ms sql left join and where problem

Posted on 2010-09-05
1
307 Views
Last Modified: 2012-05-10
I have thos sql query, works fine:

select * from (SELECT hosp,req_n,clas,marca,descr,req.fecha as fechar,titulo,(select count(*) from resp where req=req_n and ok=-1) as quan, ROW_NUMBER() OVER (ORDER BY req_n desc) AS ROWID   from req) as req left join resp as resp on resp.req=req.req_n left join usuarios on resp.prov=usuarios.id_u where rowid between 1 and 10

 need to add a where ok=-1:

on resp.req=req.req_n where ok=-1 left join

I get an error: Incorrect syntax near the keyword 'left'. How can I handle this condition.

Withput the last left join it works

select * from (SELECT hosp,req_n,clas,marca,descr,req.fecha as fechar,titulo,(select count(*) from resp where req=req_n and ok=-1) as quan, ROW_NUMBER() OVER (ORDER BY req_n desc) AS ROWID   from req) as req left join resp as resp on resp.req=req.req_n where ok=-1


0
Comment
Question by:robrodp
1 Comment
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 33607912
Two possibilities exist:

select * from
  (SELECT hosp,req_n,clas,marca,descr,req.fecha as fechar,titulo,
     (select count(*) from resp where req=req_n and ok=-1) as quan,
     ROW_NUMBER() OVER (ORDER BY req_n desc) AS ROWID   from req) as req
 left join resp as resp on resp.req=req.req_n AND resp.ok = -1
 left join usuarios on resp.prov=usuarios.id_u
where rowid between 1 and 10

select * from
  (SELECT hosp,req_n,clas,marca,descr,req.fecha as fechar,titulo,
     (select count(*) from resp where req=req_n and ok=-1) as quan,
     ROW_NUMBER() OVER (ORDER BY req_n desc) AS ROWID   from req) as req
 left join resp as resp on resp.req=req.req_n
 left join usuarios on resp.prov=usuarios.id_u
where rowid between 1 and 10  AND resp.ok = -1

They are a little bit different, so you have to check results
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server - Set Value of Multiple Fields in One Query 10 26
How to use three values with DATEDIFF 3 35
SQL Syntax 24 46
IIF in access query 19 28
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question