SQL SERVER FIND ROWS that dont have values in another table

wpx232ag 于 2023-03-07 发布在 SQL Server

关注(0)|答案(3)|浏览(179)

i have 3 tables with one to many relation {Downloads , TagNames, File_Tag}

the download table Like
| ID | File | _modifieddate |
| ------------ | ------------ | ------------ |
| 1 | FILE1.ZIP | 2/1/2023 |
| 2 | FILE2.ZIP | 3/1/2023 |
| 3 | FILE3.ZIP | 4/1/2023 |
| 4 | FILE4.ZIP | 5/1/2023 |

the TagNames table Like

ID	TagName
1	system
2	pics
3	framework
4	compressed
5	info

the File_Tag table Like

ID	FileID	TagID
1	1	1
2	1	3
3	1	4
4	2	4
5	2	2
6	3	1
7	3	4
8	4	5
9	4	4

what i want all downloads table records that dont have 'pics' or 'info' tag related to it

i tried

select * from (
SELECT Downloads._modifieddate, Downloads.File, Downloads.ID, ROW_NUMBER() over ( ORDER BY Downloads._modifieddate DESC ) AS aa FROM
 TagNames INNER JOIN File_Tag ON TagNames.ID = File_Tag.TagID INNER JOIN  Downloads ON File_Tag.FileID = Downloads.ID WHERE  (TagNames.TagName <> 'pics' ) and (TagNames.TagName <> 'info' )) as product

the row_number for paging purpose but it return file2.zip because it has another row with compressed tag and it give me duplicates for every tag related to the file

what i expect
| ID | File | _modifieddate |
| ------------ | ------------ | ------------ |
| 1 | FILE1.ZIP | 2/1/2023 |
| 3 | FILE3.ZIP | 4/1/2023 |

sql-server

来源：https://stackoverflow.com/questions/75643136/sql-server-find-rows-that-dont-have-values-in-another-table

3条答案

按热度按时间

wj8zmpe11#

You need an anti-join, which in SQL is normally done using a NOT EXISTS

SELECT
  d.ID
  d.File
  d._modifieddate
FROM Download d
WHERE NOT EXISTS (SELECT 1
    FROM File_Tag ft
    JOIN TagNames t ON t.ID = ft.TagID
    WHERE ft.FileID = d.ID    -- join condition
      AND t.TagName IN ('pics', 'info')
);

Make sure you have the join condition inside the subquery, or you will get incorrect results.

If you want Downloads which do not have both tags, but don't mind if they only have one, then use relational division. There are a number of ways to do this, here is a simple one:

SELECT
  d.ID
  d.File
  d._modifieddate
FROM Download d
WHERE NOT EXISTS (SELECT 1
    FROM File_Tag ft
    JOIN TagNames t ON t.ID = ft.TagID
    WHERE ft.FileID = d.ID    -- join condition
      AND t.TagName IN ('pics', 'info')
    HAVING COUNT(*) = 2    -- NEW!!
);

赞(0）回复(0）举报 2023-03-07

wz1wpwve2#

select d.id,d.file, d.modifieddate
from downloads as d
where not exists
(
  select 1 
  from file_tag as ftag
  join TagNames as tag on ftag.tagid=tag.id
  where tag.TagName in('pics','info')and d.id=ftag.fileid
)

May be something like this

赞(0）回复(0）举报 2023-03-07

7fyelxc53#

What you want is something like:

select * from downloads where id not in (select fileid from file_tag where tagid in (select id from TagNames where TagName in ('pics','info')))

赞(0）回复(0）举报 2023-03-07

我来回答

SQL SERVER FIND ROWS that dont have values in another table

3条答案

相关问题

热门标签

最新问答