CODEWITHSUNDEEP
sql-serversql-server-2008
Patrick
Patrick

Reputation: 2577

Search child rows for values

I have something like this: 

Transaction    Customer
1              Cust1
2              Cust2
3              Cust3
4              Cust4

TransID     Code
2           A
2           B
2           D
3           A
4           B
4           C

If I want to be able to do something like "IF Customer 'Cust1' Has code 'A'", how should I best build a view? I want to end up being able to query something like "Select Customer from View where Code in [Some list of codes]" OR "Select Cust1 from View Having Codes in [Some list of codes]"

While I can do something like 

Customer       |   Codes 
Cust1          |   A, B, D 
Etc. 

SELECT Transaction from Tbl where Codes like 'A'

This seems to me to be an impractical way to do it.

Upvotes: 0

Views: 36

Answers (2)

Jason A. Long
Jason A. Long

Reputation: 4442

Here's another option...

IF OBJECT_ID('tempdb..#CustomerTransaction', 'U') IS NOT NULL 
DROP TABLE #CustomerTransaction;

CREATE TABLE #CustomerTransaction (
    TransactionID INT NOT NULL PRIMARY KEY,
    Customer CHAR(5) NOT NULL 
    );
INSERT #CustomerTransaction (TransactionID, Customer) VALUES
    (1, 'Cust1'), (2, 'Cust2'), (3, 'Cust3'),
    (4, 'Cust4'), (5, 'Cust5');

IF OBJECT_ID('tempdb..#TransactionCode', 'U') IS NOT NULL 
DROP TABLE #TransactionCode;

CREATE TABLE #TransactionCode (
    TransactionID INT NOT NULL,
    Code CHAR(1) NOT NULL 
    );
INSERT #TransactionCode (TransactionID, Code) VALUES
    (2, 'A'), (2, 'B'), (2, 'D'), (3, 'A'), (4, 'B'), (4, 'C');

--SELECT * FROM #CustomerTransaction ct;
--SELECT * FROM #TransactionCode tc;
--=============================================================

SELECT 
    ct.TransactionID,
    ct.Customer,
    CodeList = STUFF(tcx.CodeList, 1, 1, '')
FROM 
    #CustomerTransaction ct
    CROSS APPLY (
                SELECT 
                    ', ' + tc.Code
                FROM 
                    #TransactionCode tc
                WHERE 
                    ct.TransactionID = tc.TransactionID
                ORDER BY
                    tc.Code ASC
                FOR XML PATH('')
                ) tcx (CodeList);

Results...

TransactionID Customer CodeList
------------- -------- -----------
1             Cust1    NULL
2             Cust2     A, B, D
3             Cust3     A
4             Cust4     B, C
5             Cust5    NULL

Upvotes: 1

Xedni
Xedni

Reputation: 4695

Here's how I'd do it

;with xact_cust (xact, cust) as
(
    select 1, 'cust1' union all
    select 2, 'cust2' union all
    select 3, 'cust3' union all
    select 4, 'cust4' 
), xact_code (xact, code) as
(
    select 2, 'A' union all
    select 2, 'B' union all
    select 2, 'D' union all
    select 3, 'A' union all
    select 4, 'B' union all
    select 4, 'C' 
)
select Cust, Code
from xact_cust cust
inner join xact_code code
    on cust.xact = code.xact
where exists (select 1
              from xact_code i
              where i.xact = code.xact
                  and i.code = 'A')

If you NEED the codes serialized into a delimited list, take a look at this article: What this query does to create comma delimited list SQL Server?

Upvotes: 1

Related Questions