SQL Server, one-to-many table call

Worktable
ID, task number...
Work Details Table
ID, Task ID, Task Start Time, Task End Time...
Work Details Table (Work Details Table 1 - Multi-Work Details Table)
ID, Work Details ID, Work Description, Accommodation Fee...

As I recall, there is a table that looks like this, and I would like to know the work number that does not contain the string hogehoge or nullnull in the work content.
I can't do it even if I use the sub-query through trial and errorIt looks like the following
Work detail table and work detail table are one-to-many
For example, if one work detail table has three work detail tables, I would like to extract all three work numbers that do not contain hogehoge and nullnull, but I cannot

SELECT Task Number
FROM Task Table INNER JOIN Task Detail Table ON Task Table .ID = Task Detail Table . Task ID
WHERE Task Detail Table .ID IN
(SELECT Task Detail ID)
FROM WORK DETAIL TABLE
WHERE Task Description NOT LIKE '%hogehoge%' AND Task Description NOT LIKE '%null%')

This didn't work either

SELECT distinct Task Number
FROM WORK TABLE, WORK DETAIL TABLE, WORK DETAIL TABLE
WHERE
Task Table .ID = Task Detail Table . Task ID AND
Task Detail Table .ID = Task Detail Table . Task Detail ID AND
NOT (Work Details Table.Work Description LIKE '%hogehoge%'OR
Statement of Work table.Work Description LIKE '%null%')

How can I extract a task number that does not contain a specific string?Please let me know

sql-server

2022-09-30 18:40

1 Answers

You can exclude the "Work ID" from all "Work ID" that contains the string.

The former is

SELECT ID FROM Worktable

The latter is

SELECT task details table.Task ID
  FROM Task Detail Table
 INNER JOIN WORK DETAIL TABLE
    ON Task Detail Table .ID = Task Detail Table . Task Detail ID
 WHERE Task LIKE '%hogehoge%'
    OR Task Description LIKE '%null%'

Because you can retrieve it in , you can use the EXCEPT operator to:

SELECT ID FROM Worktable
EXCEPT
SELECT Task Details Table. Task ID
  FROM Task Detail Table
 INNER JOIN WORK DETAIL TABLE
    ON Task Detail Table .ID = Task Detail Table . Task Detail ID
 WHERE Task LIKE '%hogehoge%'
    OR Task Description LIKE '%null%'

2022-09-30 18:40

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656