How to join tables when there are duplicates in right table

Question

I have three tables. Table Cust has a custID field, plus various other values (name, address etc)

Table List has a single column ID. Each ID is a custID in the Cust table Edit: the purpose of this is to filter the records, restricting thge results to ones where the CustID appears in the list table.

All three tables are indexed.

Table Trans has a TransactionID field, a Cust field that holds a customer ID, And other transaction fields

Edit: I should have mentioned that in some cases there will be no transaction record. In this case I want one row of Customer info with the transaction fields null or blank.

I want a query to return cust and transaction ID for each ID in the list table. If there is more than one matching row in the transaction table, I want each included along 3with the matching cust info. So if the tables look like This:

    Cust
ID        Name
01        John
02        Mary
03        Mike
04        Jane
05        Sue
06        Frank

    List
ID
01
03 
05
06

      Transact
TransID  CustId  Msg
21        01     There   
22        01     is
23        02     a
24        03     tide
25        04     in
26        04     the
27        05     affairs
28        05     of
29        05     men

I want the result set to be:

CustID   Name  TransID  Msg
01      John   21       There
01      John   22       is
03      Mike   24       tide
05      Sue    27       affairs
05      Sue    28       of
05      Sue    29       men
06      Frank  --       --

(Where -- represents NULL or BLANK)

Obviously the actual tables are much larger (millions of rows), but that shows the pattern, one row for every item in table Transactions that matches any of the items in the List table, with matching fields from the Cust table. if there is no matching Transaction, one row of customer info from each ID in the List table. CustID is unique in the Cust and List tables, but not in the transaction table.

This needs to work on any version of SQL server from 2005 onward, if that matters.

Any suggestions?

I'm not quite sure what the issue seems to be, as a join will bring back _all_ records which match the join clause. Additionally, the list table doesn't seem to belong in the mix, unless there's a purpose which you haven't mentioned. You can join straight from the cust table to the transaction one. Please update your question and add the query which you have tried. If you are getting incorrect results, we can try to help you along with that. — Eli, Sep 11 '17 at 18:40
The list table is being used to restrict the IDs returned. In the toy example above, it prevents any results being displayed for CustID values 2 or 4. In my Test system, it reduces tens of thousands of records to a few hundered, and at a cleint site it reduces millions of records in the cust or transaction tables to 1-2 thousand results — David Siegel, Sep 11 '17 at 19:21
In a proper system (foreign keys, indexes) you should be able to perform just fine without that list table. What have you tried to get this to work? — Eli, Sep 11 '17 at 19:30
@eli, the list table is being used as a replacement for an IN clause that caused major performance problems. It is dynamically created, and will exist only for the one query. I have tried inner joins and left joins, with both the cust table first and the trans table firsat, and a cross-apply of the trans table (part of the suggestion made at https://stackoverflow.com/questions/45766195/slow-two-table-query-in-sql-server) — David Siegel, Sep 11 '17 at 19:38
without sample data, there's only so much which can be done. Please try the solution from @Xedni with the slight change in making the inner join into a left join (thereby showing records from the customer table regardless of their appearance in the transaction table — Eli, Sep 11 '17 at 20:13

score 0 · Answer 1 · answered Sep 11 '17 at 18:41

0

Unless I'm missing something, this is all you need to do:

Select   T.CustID, C.Name, T.TransID, T.Msg
From     Transact   T
Join     Cust       C   On  C.Id = T.CustId
Join     List       L   On  L.Id = C.Id
Order By T.CustID, T.TransID

answered Sep 11 '17 at 18:41

Siyual

16,415
8
44
58

score 0 · Answer 2 · answered Sep 11 '17 at 19:02

;with cust (id, name) as
(
    select 1, 'John' union all
    select 2, 'Mary' union all
    select 3, 'Mike' union all
    select 4, 'Jane' union all
    select 5, 'Sue'
), list (id) as
(
    select 1 union all
    select 3 union all
    select 5
), transact (TransId, CustId, Msg) as
(
    select 21, 1, 'There '  
    union all select 22, 1, 'is'
    union all select 23, 2, 'a'
    union all select 24, 3, 'tide'
    union all select 25, 4, 'in'
    union all select 26, 4, 'the'
    union all select 27, 5, 'affairs'
    union all select 28, 5, 'of'
    union all select 29, 5, 'men'
)
select
    CustId = c.id,
    Name = c.Name,
    TransId = t.TransId,
    Msg = t.Msg
from cust c
inner join list l
    on c.id = l.id
inner join transact t
    on l.id = t.custid

yields:

CustId      Name TransId     Msg
----------- ---- ----------- -------
1           John 21          There 
1           John 22          is
3           Mike 24          tide
5           Sue  27          affairs
5           Sue  28          of
5           Sue  29          men

This almost works, it doesn't give me the answer i want when an ID included in the list and cust tables has no matching trans row. I failed to mention that in my initial post. — David Siegel, Sep 11 '17 at 19:40
@DavidSiegel please update your post to include this - most readers won't find it down here — Eli, Sep 11 '17 at 19:52
Change the join to `transact` to be a `left outer join` then.That will include all cust/list rows regardless of whether there are transactions — Xedni, Sep 11 '17 at 20:10

How to join tables when there are duplicates in right table

2 Answers2