Solved

Ms sql left join and where problem

Posted on 2010-09-05
1
302 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 41

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now