CODEWITHSUNDEEP
sql
Dai
Dai

Reputation: 155145

Cross Join on a subset of 2 tables

I'm attempting to copy data from an old database design to a new database design.

The database is for a multi-tenancy application, and the source table in question is a table of widget category names.

Each tenant has their own private set of category names, so the old table looks like this:

CREATE TABLE OldCategories (
    TenantId     bigint,
    CategoryName nvarchar(255)
)

OldCategories:
TenantId, CategoryName
       1, 'Dog Toys'
       1, 'Cat Toys'
       1, 'Lizard Toys',
       2, 'Dog Toys'
       2, 'Duck Toys'

The new database adds an intermediate table such that all of a tenant's categories must be duplicated for each row in the intermediate table, here is the new schema:

CREATE TABLE NewCategoryGroups (
    TenantId  bigint,
    GroupId   bigint IDENTITY,
    GroupName nvarchar(255)
)

CREATE TABLE NewCategories (
    GroupId      bigint,
    CategoryName nvarchar(255)
    CONSTRAINT PRIMARY KEY ( GroupId, CategoryName )
)

Here's what's in the NewCategoryGroups table:

NewCategoryGroups:
TenantId, GroupId, GroupName
       1,       1, '2006 Toys'
       1,       2, '2010 Toys'
       1,       3, '2016 Toys'
       2,       4, '2007 Toys'
       2,       5, '2010 Toys'

And here is the example data I want to get for the NewCategories table:

NewCategories 
GroupId, CategoryName
      1, 'Dog Toys' -- Groups 1 through 3 are for Tenant 1
      1, 'Cat Toys'
      1, 'Lizard Toys',
      2, 'Dog Toys'
      2, 'Cat Toys'
      2, 'Lizard Toys',
      3, 'Dog Toys'
      3, 'Cat Toys'
      3, 'Lizard Toys',
      4, 'Dog Toys' -- Groups 4 and 5 are for Tenant 2
      4, 'Duck Toys'
      5, 'Dog Toys'
      5, 'Duck Toys'

I've tried a CROSS JOIN, as-in:

SELECT
    CategoryName
FROM
    OldCategories
    CROSS JOIN NewCategoryGroups

...except this loses the TenantId distinction, so every tenant will get every other tenant's categories.

Upvotes: 0

Views: 205

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You may want to use an INNER JOIN instead

SELECT
    g.GroupId,
    c.CategoryName
FROM OldCategories c
INNER JOIN NewCategoryGroups g
    ON g.TenantId = c.TenantId

Upvotes: 1

Related Questions