SQL Server, inner joins

Question

SQL Server, inner joins

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

I have a question with an SQL server query. I explain:

What I would like to know is if there is the possibility of conditioning a search according to a field.

I would like something like this:

SELECT * FROM prestamos pre
INNER JOIN polizas pol ON pre.id_poliza = pol.id_poliza and 
if (pre.id_status == 11) 
     //Aqui buscar en tabla endosos los que cumplan condicion
     //Algo asi como: 
     INNER JOIN endosos eds ON pol.id_poliza = eds.id_poliza and eds.tipo_endoso = 9
else if(pre.id_status in (5,7,14))
     // No hacer nada.

Something like that, I know what is written is wrong, I hope you understand the idea of what I want, only when id_status is equal to 11 to perform the other search. The expected result would be all the loans with id_stattus 5,7,14 and 11 but the 11 status must comply with tipo_endoso = 9

Could you tell me how to solve it?

Greetings.

/// UPDATE.

Declare @fechaIn as datetime
set @fechaIn = '2017/03/09'
set @fechaIn = replace(convert(NVARCHAR, @fechaIn, 111), '/', '-')
SELECT 
pre.ID_FolPrestamo
, pre.ID_Poliza
, pre.Fecha_StaPtoPB
, pre.ID_StaPtoPB
, eds.ID_Endoso
FROM Prestamos_PB pre
INNER JOIN Polizas pol 
    ON pre.ID_Poliza = pol.ID_Poliza
LEFT JOIN Endosos eds 
    ON pol.ID_Poliza = eds.ID_Poliza
WHERE   (pre.ID_StaPtoPB in (5,7,14) 
        OR ( pre.ID_StaPtoPB = 11 AND eds.ID_Endoso = 9))
        AND Year(pre.Fecha_StaPtoPB) = Year(@fechaIn)
        AND Month(pre.Fecha_StaPtoPB) = Month(@fechaIn)
        AND day(pre.Fecha_StaPtoPB) = Day(@fechaIn)
order by pre.ID_FolPrestamo asc

The result is this:

173153  90913   2017-03-09 13:21:11.787 11  9
206820  85985   2017-03-09 13:09:06.243 11  9
216861  102548  2017-03-09 13:03:17.487 14  NULL
216922  5445    2017-03-09 13:03:14.720 14  8
216922  5445    2017-03-09 13:03:14.720 14  7
216922  5445    2017-03-09 13:03:14.720 14  10
216922  5445    2017-03-09 13:03:14.720 14  25
216922  5445    2017-03-09 13:03:14.720 14  25
216938  64048   2017-03-09 13:03:08.590 7   NULL
216976  106401  2017-03-09 13:03:05.210 14  NULL
217013  21256   2017-03-09 13:03:02.480 5   25
217013  21256   2017-03-09 13:03:02.480 5   25

As you can see, I do not want the records to be repeated, for example '216922 5445' has several lines since it has several endorsements. What I do not want is for the JOIN to do when status = 5,7,14

sql sql-server query

asked by Osiel Candido Onofre 09.11.2017 в 18:27

source

2 answers

1

I leave you another option that is based on Patricio's response, but that simplifies a bit the LEFT JOIN to avoid having to make additional groupings:

SELECT * 
FROM prestamos pre
INNER JOIN polizas pol
  ON pre.id_poliza = pol.id_poliza
LEFT JOIN endosos eds 
  ON pol.id_poliza = eds.id_poliza
  AND eds.tipo_endoso = 9
where pre.id_status in (5,7,14)
   or (pre.id_status = 11 AND eds.id_poliza is not null)

answered by 09.11.2017 в 19:33

Error calling functions How to get a time range comparing two dates in Android?

score 2 · Accepted Answer

Let's go to the essentials of your question:

The expected result would be all the loans with id_stattus 5,7,14 and 11 but the 11 status must comply with type_endoso = 9

I understand that you can solve it like this:

  SELECT * 
    FROM prestamos pre
    INNER JOIN polizas pol 
        ON pre.id_poliza = pol.id_poliza
    LEFT JOIN (SELECT   id_poliza, tipo_endoso
        FROM endosos eds 
        WHERE tipo_endoso = 9
        GROUP BY id_poliza, tipo_endoso
    ) eds
        ON pol.id_poliza = eds.id_poliza
    WHERE   pre.id_status in (5,7,14) 
            OR ( pre.id_status = 11 AND eds.tipo_endoso = 9)

Comments:

With a subquery we obtain the policies that have a tipo_endoso = 9 that we will use by a LEFT JOIN to have the additional condition for id_status = 11
The conditions are handled directly in WHERE and I think they are easy to understand