Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Ms sql left join and where problem

Posted on 2010-09-05
1
Medium Priority
?
310 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 43

Accepted Solution

by:
pcelba earned 2000 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

705 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