Search child rows for values

Question

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.

@TheGameiswar That's the problem, I'm not sure what I should have. I could make something like 'TransID | Code 1 | Code 2 | Code 3 ... ` but that seems impractical. — Patrick Schomburg, Sep 13 '17 at 17:49
Well if you don't know what the expected results should be, how could we code something against it? what's the real problem you are trying to sove / answer here? — S3S, Sep 13 '17 at 17:50
If you don't know what you want, how are we supposed to suggest something? — Xedni, Sep 13 '17 at 17:50
I tried to clarify. My end goal here is to make a view that I can easily query a certain way, I just can't figure how that particular view should look. — Patrick Schomburg, Sep 13 '17 at 17:52
And what constitutes being "easily queryable in a certain way"? As written, it seems easily queryable. Unless you have some weird case where you need it in a certain format, what's wrong with leaving it as is? — Xedni, Sep 13 '17 at 17:54
Do you need the codes serialized? Or are you just doing that as a means to search the codes? — Xedni, Sep 13 '17 at 17:56
I'd suggest just doing `select TransId, Code from tbl o where exists (select 1 from tbl i where i.transid = o.transid and i.code = 'A')` — Xedni, Sep 13 '17 at 17:57
Sorry, I made a serious error in my data. There is a column in the first table that is NOT present in the second, and I want to match that column to certain records in the second table. I'm sure that's at least part of the reason this question didn't make sense. — Patrick Schomburg, Sep 13 '17 at 17:57

score 1 · Accepted Answer · answered Sep 13 '17 at 18:00

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?

Okay, this makes sense, it's exactly what I was failing to properly ask for, I'll just add a `group by cust` to get a single row for each one with the code I want. — Patrick Schomburg, Sep 13 '17 at 18:07

score 1 · Answer 2 · answered Sep 13 '17 at 18:23

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

Would I need to use the like operator to query these results further? — Patrick Schomburg, Sep 13 '17 at 18:30
@PatrickSchomburg - No. You's want to do any additional filtering by adding a WHERE clause to the outer query to filter transactions or customers... or add to the existing WHERE in the sub-query to filter codes. — Jason A. Long, Sep 13 '17 at 18:40

Search child rows for values

2 Answers2