Member Avatar for ETbo

Hello guys,

I have two tables with the following structure:

TABLE A:
Email Address, CampaignID, Source, Unsubscribe
Email1,2
Email1,1
Email1,3
Email2,5

TABLE B:
Email Address, SeedList, ListName
Email1,SeedListA.SeedListA
Email1,SeedListB,SeedListB
Email2,SeedListC,SeedListC

The purpose is to merge the two tables together. But there is a really strange rationale behind this merge. If there are records of an Email Address in Table A, for instance 3 records, then for each and every record in Table A, I have to iterate all the records with the same Email Address from Table B.

Example:

MERGE TABLE:
Email Address, CampaignID, Source, Unsubscribe, SeedList, ListName
Email1 | 2 | 5 | yes | SeedList A | Seed List A
Email1 | 2 | 5 | yes | SeedList B | Seed List B
Email1 | 1 | 5 | yes | SeedList A | Seed List A
Email1 | 1 | 5 | yes | SeedList B | Seed List B
Email1 | 3 | 5 | yes | SeedList A | Seed List A
Email1 | 3 | 5 | yes | SeedList B | Seed List B
Email2 | 5 | 5 | yes | SeedList C | Seed List C

Does it make any sense?

Any assistance will be highly appreciated guys! Thanks a lot!

  • 2 Contributors
  • 2 Replies
  • 104 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by ETbo

Dani AI

Generated

Yes — that is exactly what was pointing to: joining A and B on the email will produce every pair of matching rows. If TableA has N rows for a given email and TableB has M rows for the same email, the result will contain N * M rows. That is the normal set-based behavior of an inner join, not a bug.

Which query to use depends on the business need:

  • If you truly want every campaign row from A paired with every seed list row from B, an inner join is correct.
  • If you want to keep A rows even when B has no match, use a LEFT JOIN.
  • If you want one combined B value per email (so each A row joins to a single aggregated B value), aggregate B first and then join. Example (SQL Server 2017+):
-- produce one concatenated list of ListName per email, then join to A
SELECT a.[Email Address], a.CampaignID, a.Source, a.Unsubscribe, b.SeedLists
FROM TableA a
LEFT JOIN (
  SELECT [Email Address], STRING_AGG(ListName, ', ') AS SeedLists
  FROM TableB
  GROUP BY [Email Address]
) b ON b.[Email Address] = a.[Email Address];

If instead you want exactly one row from B per A row (for example "the most recent" or some preferred seed), use APPLY or a ROW_NUMBER() window to pick a single B row per email:

-- pick one B row per A row (adjust ORDER BY to your preference)
SELECT a.*, b.SeedList, b.ListName
FROM TableA a
OUTER APPLY (
  SELECT TOP (1) SeedList, ListName
  FROM TableB b2
  WHERE b2.[Email Address] = a.[Email Address]
  ORDER BY b2.SomeDate DESC
) b;

Practical tips: index the join column(s) on both tables; ensure email collation/casing/whitespace are consistent (or keep a normalized persisted column you can index); avoid applying functions on the join key (they stop index seeks). If you plan to denormalize into a flattened table for reporting, do it as a controlled ETL so you avoid update anomalies and keep counts verifiable (for each email expect count = countA * countB unless you aggregate first).

Recommended Answers

All 2 Replies

Member Avatar for Momerath

SELECT a.[Email Address], a.CompaignID, a.Source, a.Unsubscribe, b.SeedList, b.ListName from TableA a, TableB b
WHERE a.[Email Address] = b.[Email Address]
ORDER BY a.[Email Address]

Member Avatar for ETbo

Is it that simple? Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.