Joining two tables using wild cards to populate a third

Question

Joining two tables using wild cards to populate a third

Garth Zaleschuk

SSC Veteran

Points: 288
More actions
July 18, 2019 at 6:55 pm

#3664291

Good day folks, I could use some help, please.
I am trying to populate a user access table:
User_name, User_country, User_company, User_branch
My first source table is :
User_name, User_country, User_company, User_branch
Joe, CA,01,* (or I could use %)
Sally,CA,*,*
Fred,*,*,*
Wilma,CA,01,05
Barney,*,*,10
My second source table is :
User_country, User_company, User_branch
CA,01,10
CA,01,20
CA,01,30
CA,02,10
CA,02,20
CA,03,10
US,01,10
US,02,10
US,02,20
US,03,10
The result I am looking for is :
User_name, User_country, User_company, User_branch
Joe,CA,01,10
Joe,CA,01,20
Joe,CA,01,30
Sally,CA,01,10
Sally,CA,01,20
Sally,CA,01,30
Sally,CA,02,10
Sally,CA,02,20
Sally,CA,03,10
Fred,CA,01,10
Fred,CA,01,20
Fred,CA,01,30
Fred,CA,02,10
Fred,CA,02,20
Fred,CA,03,10
Fred,US,01,10
Fred,US,02,10
Fred,US,02,20
Fred,US,03,10
Barney,CA,01,10
Barney,CA,02,10
Barney,CA,03,10
Barney,US,01,10
Barney,US,02,10
Barney,US,03,10
Note: Wilma gets no records as there is no Branch 05

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

rVadim Hall of Fame Points: 3969 More actions · Answer 1

If you present your data in more easily consumable way, more people will be willing to help.

CREATE TABLE #UserA(
  [User_name] VARCHAR(20), 
  User_country CHAR(2), 
  User_company VARCHAR(10), 
  User_branch VARCHAR(10)
);
INSERT INTO #UserA (
  [User_name], 
  User_country,
  User_company,
  User_branch
)
VALUES ('Joe', 'CA', '01', '*') 
      ,('Sally','CA','*','*')
      ,('Fred','*','*','*')
      ,('Wilma','CA','01','05')
      ,('Barney','*','*','10')

CREATE TABLE #UserB(
  User_country CHAR(2), 
  User_company VARCHAR(10), 
  User_branch VARCHAR(10)
);

INSERT INTO #UserB (
  User_country,
  User_company,
  User_branch
)
VALUES 
('CA','01','10'),
('CA','01','20'),
('CA','01','30'),
('CA','02','10'),
('CA','02','20'),
('CA','03','10'),
('US','01','10'),
('US','02','10'),
('US','02','20'),
('US','03','10')

--SELECT * FROM #UserA
--SELECT * FROM #UserB

SELECT A.[User_name], B.*
FROM #UserA AS A
  INNER JOIN #UserB AS B
    ON  A.User_country = CASE WHEN A.User_country <> '*' 
                              THEN B.User_country 
                              ELSE '*' 
                         END
    AND A.User_company = CASE WHEN A.User_company <> '*' 
                              THEN B.User_company 
                              ELSE '*' 
                         END
    AND A.User_branch  = CASE WHEN A.User_branch <> '*' 
                              THEN B.User_branch 
                              ELSE '*' 
                         END
ORDER BY A.[user_name];

DROP TABLE #UserA;
DROP TABLE #UserB;

--Vadim R.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

I don't know about the overall logic, but the JOINs above seem needlessly convoluted. Instead, maybe:

  INNER JOIN #UserB AS B
    ON  (A.User_country = '*' OR A.User_country = B.User_country)
    AND (A.User_company = '*' OR A.User_company = B.User_company)
    AND (A.User_branch  = '*' OR A.User_branch  = B.User_branch)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".