Extract only a type of data from a table

Transact-SQL
1

Hello,

I am new to SQL server and I struggle a little with something, maybe is very easy but i just can't solve it. I have some countries with stores. This is my example table:
image

create table my_table
(
[ID] [nvarchar]2 NULL,
[Country] [nvarchar]20 NULL,
[Markets] [nvarchar]50 NULL
)

insert into my_table(ID,Country,Markets)
values('AL', 'Albania','C&A')
values('AL', 'Albania','Douglas')
values('AL', 'Albania','')
values('AD','Andorra','C&A')
values('AD','Andorra','')
values('EE','Estonia','Sephora')
values('EE','Estonia','Douglas')
values('EE','Estonia','')

I only need to extract the countries that have only C&A and blancks in the Market column.
In may case is only Andorra. Even if Albania has C&A and blanck , it also have Douglas and thats why i dont need that country.

my code is:

select distinct ID into #table1 from my_table
where Markets='C&A'

select * into #table2 from my_table
where [ID] in (select distinct [ID] from #table1 )

I know the mistake because it takes also Albania, not only Andorra because of the #table1

I cant figre it out how to substarct only the country with only C&A and blancks thats all.

I would appreciate any help if its possible :smiley:

Thank you in advance!

2

Try this:

select id
  into #table2
  from #table1
 group by id
 having sum(case when markets in ('C&A','') then 0 else 1 end)=0
;
3

Im sorry but its not working :frowning:
Thank you anyways

4

hi

I have tried it

please see my solution if it works
:slight_smile:
:slight_smile:

drop create data 
use tempdb
go 


drop table my_table 
go 


create table my_table
(
[ID] varchar(2) NULL,
[Country] varchar(20) NULL,
[Markets] varchar(50) NULL
)

insert into my_table(ID,Country,Markets)
values('AL', 'Albania','C&A'),
('AL', 'Albania','Douglas'),
('AL', 'Albania',''),
('AD','Andorra','C&A'),
('AD','Andorra',''),
('EE','Estonia','Sephora'),
('EE','Estonia','Douglas'),
('EE','Estonia','')
go 

select * from my_table
go
SQL 
SELECT id 
FROM   my_table 
WHERE  markets LIKE '' 
INTERSECT 
SELECT id 
FROM   my_table 
WHERE  markets LIKE 'C&A' 
EXCEPT 
SELECT id 
FROM   my_table 
WHERE  markets LIKE 'Douglas' 

go
Results

image

5

the "Except" statement from harishgg1 will work as long as you know the only other option is Douglas, but if it isn't then you'll have the same issue. Here's a different solution using only what you want. One question though, I know you're data was using empty string (''), but is it null or really an empty string? I've coded for if it is null as well

Drop table if exists #My_table

create table #my_table
(
[ID] [nvarchar] (2) NULL,
[Country] [nvarchar] (20) NULL,
[Markets] [nvarchar] (50) NULL
)

insert into #my_table(ID,Country,Markets)
values('AL', 'Albania','C&A'),
('AL', 'Albania','Douglas'),
('AL', 'Albania',''),
('AD','Andorra','C&A'),
('AD','Andorra',''),
('EE','Estonia','Sephora'),
('EE','Estonia','Douglas'),
('EE','Estonia','')


select *
from #my_table m
where m.Markets = 'C&A'
and exists (select 1 from #my_table m1 where m.ID = m1.ID and IsNull(m1.Markets,'') = '')
union all
select *
from #my_table m
where IsNull(m.Markets,'') = ''
and exists (select 1 from #my_table m1 where m.ID = m1.ID and m1.Markets = 'C&A')
6

hello,

i know for sure that the Markets column had blanks not nulls.
But unfortunately it doesnt work because i see that it selects Andorra as well as Albania.
This is what i get after runnig the script:
AL Albania C&A
AD Andorra C&A
AL Albania
AD Andorra

I want only this:

AD Andorra C&A
AD Andorra

7

thank you but what i wrote was only a sample of my data. I dont know what other exceptions besides Douglas i could have :slight_smile:

8

hi

may be i could help you out
if i understood you

you mean you want
ID which has .. only 'C&A' and ' '
nothing else i mean no other data

AD Andorra C&A
AD Andorra

it should not be like this
AD Andorra C&A
AD Andorra
AD anything else ....

9

yes exactly. AS you saw, Albania besides blanks and C&A has also Douglas so i am not interested in that. But besides Douglas i could have other name of stores. That was just an example. I need to see only the countries that have only blanks and C&A. If they have blanks, C&A and other stores, i am not interested in that country.

Thank you in advance:)

10

Now that I understand what you are looking for. This works:

Drop table if exists #My_table

create table #my_table
(
[ID] [nvarchar] (2) NULL,
[Country] [nvarchar] (20) NULL,
[Markets] [nvarchar] (50) NULL
)

insert into #my_table(ID,Country,Markets)
values('AL', 'Albania','C&A'),
('AL', 'Albania','Douglas'),
('AL', 'Albania',''),
('AD','Andorra','C&A'),
('AD','Andorra',''),
('EE','Estonia','Sephora'),
('EE','Estonia','Douglas'),
('EE','Estonia','')

;with cte as (select distinct  ID, country
				from #my_table
			   where Markets not in ('C&A',''))

select m.*
from #my_table m
	left join cte c
		on m.ID = c.ID
		and m.Country = c.Country
where c.ID is null
1 Like
11 
SELECT ID
FROM dbo.my_table
GROUP BY ID
HAVING MAX(CASE WHEN Markets = 'C&A' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Markets = '' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Markets IN ('', 'C&A') THEN 0 ELSE 1 END) = 0
1 Like
12

Select country
from my_table
where Markets = 'C&A' or Markets = ' '
Except
Select country from my_table where Markets != 'Douglas'

13

Something like this would work also:

Select *
  From #my_table mt
 Where mt.Markets In ('C&A', '')
   And Not Exists (Select *
                     From #my_table mt2
                    Where mt2.ID = mt.ID
                      And mt2.Markets Not In ('C&A', ''))
14

I don't think it will, because it doesn't verify that 'C&A' and '' are both present.

15

Select a.country from my_table a inner join my_table b
on a.country = b.country
where a.Markets = 'C&A' and b.Markets = ' '
Except
Select country from my_table where Markets = 'Douglas'

I hope this works

16

thank you so much. It works :smiley: