Solved

Ms sql left join and where problem

Posted on 2010-09-05
1
308 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Date 6 42
How do i delete the last node in an xml in T-SQL 7 29
SQL Query 20 25
Error converting data type varchar to numeric. 4 13
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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