CODEWITHSUNDEEP
sql-server-2008-r2
Nickso
Nickso

Reputation: 805

SQL Way to use IN with two columns without a JOIN

I have the following query:

SELECT 
    [ID], [DESCRIPCION], [EZD]  
FROM 
    [KPTA_WEB_VW_COMBOS]
WHERE 
    [EZD] = '0000128460'
    AND [EZD] IN (SELECT DISTINCT Cod_EZD, Cod_Combo
                  FROM KPTA_REP_Venta
                  WHERE Id_Dia BETWEEN  CAST(CONVERT(VARCHAR(8),DATEADD(MONTH, -200, GETDATE()),112) AS INT)
                               AND CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS INT) 
                 )
GROUP BY 
    [ID], [DESCRIPCION], [EZD]

Now I have two questions, is the IN working with the WHERE clause written like that and I need to do the same for the [ID] column but I can't use it in the same IN, is there any way to do it?

The idea is to bring only the information of the KPTA_WEB_VW_COMBOS table only when the [ID] and [EZD] columns matches with the KPTA_REP_Venta table. I don't want to use JOIN as I don't need the data, I just want to know if the combination of those two columns exists in both tables and return what's in the GROUP BY clause.

EDIT: After searching more I changed my initial query like this:

SELECT 
    [ID],[DESCRIPCION],[EZD]  
FROM 
    [KPTA_WEB_VW_COMBOS] A
WHERE 
    [EZD] = '0000128460' --@EZD
    AND EXISTS 
    (SELECT TOP 1 1
        FROM 
            KPTA_REP_Venta B
        WHERE 
            B.Id_Dia BETWEEN  
            CAST(CONVERT(VARCHAR(8),DATEADD(MONTH, -200, GETDATE()),112) AS INT) AND CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS INT) 
            AND A.[EZD]= b.[Cod_Ezd]
            AND A.[ID] = b.[Cod_Combo]
    )
GROUP BY [ID],[DESCRIPCION],[EZD]

Upvotes: 1

Views: 26

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

It seems you are looking for something like this, where not a single column, but a list of columns shall be found with a subquery:

select id, descripcion, ezd  
from kpta_web_vw_combos
where (ezd, id) in
(
  select cod_ezd, cod_combo
  from kpta_rep_venta
  where id_dia between 
          cast(convert(varchar(8),dateadd(month, -200, getdate()),112) as int) and
          cast(convert(varchar(8),getdate(),112) as int) 
);

This is how it is donme in many DBMS, and I am pretty sure it is standard SQL, but SQL Server doesn't feature this syntax.

So re-write this with EXISTS, as you already guessed:

select id, descripcion, ezd  
from kpta_web_vw_combos c
where exists
(
  select *
  from kpta_rep_venta v
  where v.id_dia between 
          cast(convert(varchar(8),dateadd(month, -200, getdate()),112) as int) and 
          cast(convert(varchar(8),getdate(),112) as int) 
  and v.cod_ezd = c.ezd
  and v.cod_combo = c.id
);

Upvotes: 2

Related Questions