SQL Query for fetching all values of tables except where condition-CodePudding

I am writing a SQL Query for fetching all values of tables except where condition.

E.g. I want to fetch everything from the Output table where the Digital Market is present.

So that it should display all the data where Digital Market is present, and data related to Digital market should not come up.

For e.g. where Digital Market is present, then for that Brand everything should be displayed except the Digital Market data, other associated Market details should come up

select *
FROM [TABLE_OUTPUT]
where Market like 'Thailand' 
having Market = 'Digital Market'

CodePudding user response：

Wrote below query and it helped. Left join helped here:-

IF OBJECT_ID('tempDB..#reach_test') IS NOT NULL
BEGIN
DROP TABLE #reach_test
END

select distinct Primary_Brand
into #reach_test
  FROM [TABLE_OUTPUT]
  where Market  in ('Digital Market')
  order by Primary_Brand

  select * from [TABLE_OUTPUT] a
  left join #reach_test b
  on a.Primary_Brand=b.Primary_Brand

CodePudding user response：

First off the bat, the way you've done the LIKE isn't accurate:

select *
FROM [TABLE_OUTPUT]
where Market like 'Thailand'

Is effectively just:

select *
FROM [TABLE_OUTPUT]
where Market = 'Thailand'

So to fix that it depends on whether you want the string to contain Thailand, or start/end with it.

Let's go simple and just do contains:

select *
FROM [TABLE_OUTPUT]
where Market like '%Thailand%' -- Starts with would be 'Thailand%' and Ends with would be '%Thailand'

But, for what you're after it sounds like you're wanting something more complex.

Effectively, what you want is a two step process:

1: Figure out which brands have a digital market in Thailand

Let's say your hypothetical table structure is something like this, for simplicities sake:

CREATE TABLE [myTable] (
    id int, 
    location nvarchar(max),
    marketType nvarchar(max), 
    brand nvarchar(max)
)

From there, to get the brands that have a digital market in Thailand is as simple as:

SELECT brand
FROM myTable 
WHERE location = 'Thailand'
      AND marketType = 'Digital market'

2: Get their other market types

If you only want the results relating to Thailand then:

SELECT brand
INTO #myTempBrandList
FROM myTable 
WHERE location = 'Thailand'
      AND marketType = 'Digital market';

SELECT t.*
FROM myTable t
     INNER JOIN #myTempBrandList b
                ON b.brand = t.brand
WHERE t.location = 'Thailand'
      AND t.marketType != 'Digital market';

Will do the trick.

Obviously, this will need tweaking to match the structure of your data, as you haven't given any indication in your question as to what the structure may be.