SQL - Where "in" and "not in" from same column into 2 new columns

Question

I've been working on a report and I cant use @variable. So I'm trying to solve my problem with just "simple" SQL.

The problem I have is that I have one table (DEPTH) containing all locations on a warehouse. But this table doesn't tell if the location is ocupied or not. To find out I need to use "where in" or "where not in" in table (PRODUCTLOCATION)

I would like to have this as one output instead of running 2 queries. With UNION I get what I want but the format is wrong.

So LoationCount result should be where exist in 1 column and where not exist in another column

I tried so many things. As you can see I want this grouped by EquipmentType and so on

Here is my script as it is right now.

select EquipmentType, EquipmentName, LocationName, LocationCount, Used_Or_UnUsed,  LocationID, LocationType
from 
    (select et.equiptext EquipmentType, 
    e.EQUIPTEXT EquipmentName,
    s.SIZETEXT LocationName, count(*) LocationCount, 'Empty Locations' as Used_Or_UnUsed, s.sizeid LocationID ,
    case when d.LOCATIONTYPE=1 then 'Pick Location' when d.LOCATIONTYPE=2 then 'Buffer Location' else 'Unknown' end as LocationType 
    from depth d with (nolock)
    join SIZEPARAM s with (nolock) on d.SIZEID=s.SIZEID
    join POSITIONS p with (nolock) on d.POSITIONID=p.POSITIONID
    join shelf sh with (nolock) on p.SHELFID=sh.SHELFID
    join EQUIPMENT e with (nolock) on sh.EQUIPID=e.EQUIPID
    join EQUIPMENTTYPE et with (nolock) on e.EQUIPTYPEID=et.EQUIPTYPEID
    where d.DEPTHID not in (select depthid from productlocation) 
    and d.SIZEID in (select SIZEID from SIZEPARAM where SizeCategoryID in (10,20,30,40,50,60,70,210))
    group by et.EQUIPTEXT,e.EQUIPTEXT, d.SIZEid, s.SIZEID, s.SIZETEXT, d.LOCATIONTYPE
    ) t
    group by t.EquipmentType, t.EquipmentName, t.LocationName, t.LocationCount, t.Used_Or_UnUsed, t.LocationID, t.LocationType
union all   
select EquipmentType, EquipmentName, LocationName, LocationCount, Used_Or_UnUsed,  LocationID, LocationType
from 
    (select et.equiptext EquipmentType, 
    e.EQUIPTEXT EquipmentName,
    s.SIZETEXT LocationName, count(*) LocationCount, 'Used Locations' as Used_Or_UnUsed, s.sizeid LocationID ,
    case when d.LOCATIONTYPE=1 then 'Pick Location' when d.LOCATIONTYPE=2 then 'Buffer Location' else 'Unknown' end as LocationType
    from depth d with (nolock)
    join SIZEPARAM s with (nolock) on d.SIZEID=s.SIZEID
    join POSITIONS p with (nolock) on d.POSITIONID=p.POSITIONID
    join shelf sh with (nolock) on p.SHELFID=sh.SHELFID
    join EQUIPMENT e with (nolock) on sh.EQUIPID=e.EQUIPID
    join EQUIPMENTTYPE et with (nolock) on e.EQUIPTYPEID=et.EQUIPTYPEID
    where d.DEPTHID in (select depthid from productlocation) 
    and d.SIZEID in (select SIZEID from SIZEPARAM where SizeCategoryID in (10,20,30,40,50,60,70,210))
    group by et.EQUIPTEXT,e.EQUIPTEXT, d.SIZEid, s.SIZEID, s.SIZETEXT, d.LOCATIONTYPE
    ) t 
    group by t.EquipmentType, t.EquipmentName, t.LocationName, t.LocationCount, t.Used_Or_UnUsed, t.LocationID, t.LocationType
    order by t.EquipmentName asc

Please forgive my crappy formatting of this question.

Is this really MySQL and not MS SQL Server? – jarlh Oct 20 '15 at 08:38 — jarlh, Oct 20 '15 at 08:38
Nope... just forgot to remove the tag – m0rte0 Oct 20 '15 at 10:34 — m0rte0, Oct 20 '15 at 10:34

score 1 · Accepted Answer · answered Oct 20 '15 at 08:57

1

With your permission, I will not repeat the entire query. And put here the essence of what you need.

  SELECT d.depthid AS depthid
        ,CASE WHEN pr.depthid IS NULL THEN 1 END AS not_in_flag
        ,CASE WHEN pr.depthid IS NOT NULL THEN 1 END AS in_flag
    FROM depth d
    LEFT OUTER JOIN productlocation pr
      ON d.depthid = pr.depthid

answered Oct 20 '15 at 08:57

diziaq

6,881
16
54
96

Added a SUM(CASE) and removed pr.depthid from my group by. Now I have exactly what I needed. Thx! – m0rte0 Oct 20 '15 at 10:31

Thorsten Kettner · Answer 2 · 2015-10-20T09:04:12.240

Simply move d.depthid [not] in (select depthid from productlocation) from your WHERE clause (where it makes you get only the one set or the other) to your select clause with CASE WHEN:

select 
  et.equiptext as equipmenttype, 
  e.equiptext as equipmentname,
  s.sizetext as locationname, 
  count(*) as locationcount, 
  case when d.depthid in (select depthid from productlocation) then 'Used Locations' 
       else 'Empty Locations'
  end as used_or_unused, 
  s.sizeid as locationid,
  case when d.locationtype = 1 then 'pick location' 
       when d.locationtype = 2 then 'buffer location' 
       else 'unknown' 
  end as locationtype
from depth d with (nolock)
join sizeparam s with (nolock) on d.sizeid = s.sizeid
join positions p with (nolock) on d.positionid = p.positionid
join shelf sh with (nolock) on p.shelfid = sh.shelfid
join equipment e with (nolock) on sh.equipid = e.equipid
join equipmenttype et with (nolock) on e.equiptypeid = et.equiptypeid
where d.sizeid in (select sizeid from sizeparam where sizecategoryid in (10,20,30,40,50,60,70,210))
group by et.equiptext, e.equiptext, d.sizeid, s.sizeid, s.sizetext, d.locationtype
order by e.equiptext asc

SQL - Where "in" and "not in" from same column into 2 new columns

2 Answers2