SQL Server Use Exists with a Column of Query Result?

jjjwad0x 于 2023-02-18 发布在其他

关注(0)|答案(2)|浏览(157)

I have 2 tables.
One is bom_master:
| CHILD | PARENT |
| ------------ | ------------ |
| 1-111 | 66-6666 |
| 2-222 | 77-7777 |
| 2-222 | 88-8888 |
| 3-333 | 99-9999 |

Another one is library:

FileName	Location
66-6666_A.step	S:\ABC
77-7777_C~K1.step	S:\DEF

And I want to find out if the child's parents have related files in the library.
Expected Result:

CHILD	PARENT	FileName
1-111	66-6666	66-6666_A.step
2-222	77-7777	77-7777_C~K1.step

Tried below lines but return no results. Any comments? Thank you.

WITH temp_parent_PN(parentPN)
AS
(
    SELECT 
    [PARENT]
    FROM [bom_master]
    where [bom_master].[CHILD] in ('1-111','2-222')
)
SELECT s.[filename]
FROM [library] s
WHERE EXISTS 
(
    SELECT 
    *
    FROM temp_parent_PN b
    where s.[filename] LIKE '%'+b.[parentPN]+'%'
)

sql-server

来源：https://stackoverflow.com/questions/75469348/use-exists-with-a-column-of-query-result

2条答案

按热度按时间

wgxvkvu91#

If you have just one level of dependencies use the join solution proposed by dimis164.

If you have deeper levels you could use recursive queries allowed by WITH clause ( ref. WITH common_table_expression (Transact-SQL) ).

This is a sample with one more level of relation in bom_master (you could then join the result of the recursive query with library as you need).

DECLARE @bom_master TABLE (Child NVARCHAR(MAX), Parent NVARCHAR(MAX));

INSERT INTO @bom_master VALUES
    ('1-111', '66-666'),
    ('2-222', '77-777'),
    ('3-333', '88-888'),
    ('4-444', '99-999'),
    ('A-AAA', '1-111');

WITH 
    leaf AS ( -- Get the leaf elements (elements without a child)
        SELECT Child FROM @bom_master b1
        WHERE NOT EXISTS (SELECT * FROM @bom_master b2 WHERE b2.Parent = b1.Child) ),
    rec(Child, Parent, Level) AS (
        SELECT b.Child, b.Parent, Level = 1 
        FROM @bom_master b
        JOIN leaf l ON l.Child = b.Child
        UNION ALL
        SELECT rec.Child, b.Parent, Level = rec.Level + 1
        FROM rec
        JOIN @bom_master b
        ON b.Child = rec.Parent )
SELECT * FROM rec

赞(0）回复(0）举报 2023-02-18

wpcxdonn2#

I think you don't have to use exists. The problem is that you need to substring to match the join. Have a look at this:

SELECT b.CHILD, b.PARENT, l.[FileName] 
FROM [bom_master] b 
INNER JOIN [library] l ON b.PARENT  = SUBSTRING(l.FileName,1,7)

赞(0）回复(0）举报 2023-02-18

我来回答

SQL Server Use Exists with a Column of Query Result?

2条答案

相关问题

热门标签

最新问答