Get all records without fulfilling a condition

Transact-SQL
1

Hi, I have these config in postgre:
Tables

DEF

NAMEJOB
ID_JOB
ID_TABLE
....

VARIABLE

ID_JOB
ID_TABLE
NAME
VALUE
....


Registers examples:

DEF

NAMEJOB="APPLE"
ID_JOB=1
ID_TABLE=1

NAMEJOB="HOUSE"
ID_JOB=2
ID_TABLE=2

VARIABLE

ID_JOB=1
ID_TABLE=1
NAME="COLOR"
VALUE="GREEN"

ID_JOB=1
ID_TABLE=1
NAME="SIZE"
VALUE="SMALL"

ID_JOB=2
ID_TABLE=2
NAME="COLOR"
VALUE="BROWN"

......


I would like to get all registers that don't have "NAME=SIZE" IN THE TABLE VARIABLE. So the output should be:

ID_JOB=2
ID_TABLE=2
NAMEJOB=HOUSE


If I make a where with job_id and table_id shows me all registers. Someone could help me please? Thanks and sorry for my English!
2

I wanted to post only SQL, but can't, have to make it look like a "sentence" because of this site's damn parser:

SELECT VARS.ID_JOB, VARS.ID_TABLE, VARS.NAMEJOB
FROM (
    SELECT ID_JOB, ID_TABLE
    FROM [VARIABLE]
    GROUP BY ID_JOB, ID_TABLE
    HAVING MAX(CASE WHEN NAME = 'SIZE' THEN 1 ELSE 0 END) = 0
) AS VARS
INNER JOIN DEF ON DEF.ID_JOB = VARS.ID_JOB AND DEF.ID_TABLE = VARS.ID_TABLE
1 Like
3

Thanks it's a good approximation.
Gives me more registers than expected.
But I'm culprit.
I forget mention that in DEF TABLE there is other column (called TYPE) where I must obtain all of TYPE=C. Because in VARIABLE there are a lot or register and not all are of the type C.
If I use your query gives me all types and not only of type C.

Thanks and sorry for my English!

4

hi

i tried to do this

is this correct ?????

hope it helps :slight_smile: :slight_smile:

drop create data ... 
/***************************************************************************/


-- drop all temp tables 

DECLARE @SQL NVARCHAR(max) = (SELECT 'DROP TABLE ' + Stuff( ( SELECT ',' + NAME 
                                     FROM 
                                     tempdb.sys.tables FOR 
                                       xml path( 
          '') ), 1, 1, '')); 

--PRINT @SQL; 
EXEC Sp_executesql 
  @SQL; 


/***************************************************************************/
 
 -- create tables 
 -- INT VARCHAR(100)  DATE DATETIME DECIMAL(10,2) 

 create table #DEF
 (
 NAMEJOB VARCHAR(100),
ID_JOB INT,
ID_TABLE INT,
TYPE VARCHAR(100)
 )
 go 


 create table #VARIABLE
 (
ID_JOB INT,
ID_TABLE INT,
NAME VARCHAR(100),
VALUE VARCHAR(100)
)
go 

 /***************************************************************************/

 -- insert data into tables

 insert into #DEF  select 'APPLE',1,1,'C'
 insert into #DEF select 'HOUSE',2,2,'C'

 insert into #VARIABLE select 1,1,'COLOR','GREEN'
 insert into #VARIABLE select 1,1,'SIZE','SMALL'
insert into #VARIABLE select 2,2,'COLOR','BROWN'

/****************************************************************************/

-- select from all temp tables 

DECLARE @SQL1 NVARCHAR(max) = (SELECT Stuff((SELECT '; select * from ' + NAME + 
                                                    ' go ' 
                 FROM   tempdb.sys.tables 
                 FOR xml path('')), 1, 1, '')); 

--PRINT @SQL1; 
EXEC Sp_executesql 
  @SQL1; 

/***************************************************************************/
SQL .... 
SELECT * 
FROM   #def a 
WHERE  id_job IN (SELECT id_job 
                  FROM   #variable A 
                  WHERE  id_job NOT IN (SELECT id_job 
                                        FROM   #variable 
                                        WHERE  NAME = 'SIZE')) 

go

image
image.png343×532 11.7 KB