Solved - Can I join these tables with a query to obtain the final result?

Question

Solved - Can I join these tables with a query to obtain the final result?

Vegeta7

Mr or Mrs. 500

Points: 572
More actions
August 27, 2017 at 7:51 pm

#323202

Hi all,
I'm wrapping my head around a problem that I'm not being able to solve. Perhaps one of you might know what I'm failing to understand.
I have 3 tables:
In a sentence, I need to create a query that will give me the Final_Table.
For every FranchiseCode that is empty, I need to go get it from Table B through the columns FranchiseGroup and FranchiseValue.
For example:
For Table_A, ProductCode = 0003, I have an empty FranchiseCode.
So I need to look at the FranchiseGroup and FranchiseValue.
FranchiseGroup being equal to B1 means that I need to look at column B1 in Table_B, and FranchiseValue is the value I will be looking for in Column B1 to obtain the correspondent FranchiseCode.
So, by working the logic, in the Final_Table, for the ProductCode = '0003', I will have a FranchiseCode equal to 'Result1' and 'Result2'
Here are the queries to create and populate these tables:
CREATE TABLE Table_A (
    ProductCode    VARCHAR (255),
    FranchiseCode VARCHAR (255),
    FranchiseGroup VARCHAR (255),
    FranchiseValue VARCHAR (255)
    )
;
INSERT INTO Table_A
    (ProductCode, FranchiseCode, FranchiseGroup, FranchiseValue)
VALUES
    ('0001','aaaa','',''),
    ('0002','bbbb','',''),
    ('0003','','B1','Value1'),
    ('0004','','B2','Value2'),
    ('0005','dddd','',''),
    ('0006','eeee','','')
;
CREATE TABLE Table_B (
    FranchiseCode    VARCHAR (255),
    B1 VARCHAR (255),
    B2 VARCHAR (255),
    )
;
INSERT INTO Table_B
    (FranchiseCode, B1, B2)
VALUES
    ('Result1','Value1',''),
    ('Result2','Value1',''),
    ('ResultA','','Value2'),
    ('ResultB','','Value2')
;
CREATE TABLE Final_Table (
    ProductCode VARCHAR (255),
    FranchiseCode    VARCHAR (255)
    )
;
INSERT INTO Final_Table
    (ProductCode, FranchiseCode)
VALUES
    ('0001','aaaa'),
    ('0002','bbbb'),
    ('0003','Result1'),
    ('0003','Result2'),
    ('0004','ResultA'),
    ('0004','ResultB'),
    ('0005','dddd'),
    ('0006','eeee')
It seems quite simple and I was thinking about a nested case, but I can't seem to understand how to join Table_A and Table_B to get that final result.
Your help is much appreciated 🙂

Viewing 12 posts - 1 through 11 (of 11 total)

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Try something like this:CREATE TABLE #Table_A ( ProductCode VARCHAR (255), FranchiseCode VARCHAR (255), FranchiseGroup VARCHAR (255), FranchiseValue VARCHAR (255) ); INSERT INTO #Table_A (ProductCode, FranchiseCode, FranchiseGroup, FranchiseValue) VALUES ('0001','aaaa','',''), ('0002','bbbb','',''), ('0003','','B1','Value1'), ('0004','','B2','Value2'), ('0005','dddd','',''), ('0006','eeee','','');

CREATE TABLE #Table_B ( FranchiseCode VARCHAR (255), B1 VARCHAR (255), B2 VARCHAR (255), ); INSERT INTO #Table_B (FranchiseCode, B1, B2) VALUES ('Result1','Value1',''), ('Result2','Value1',''), ('ResultA','','Value2'), ('ResultB','','Value2');

CREATE TABLE #Final_Table ( ProductCode VARCHAR (255), FranchiseCode VARCHAR (255) );

INSERT INTO #Final_Table (ProductCode, FranchiseCode) SELECT A.ProductCode, CASE WHEN A.FranchiseGroup <> '' THEN B.FranchiseCode ELSE A.FranchiseCode END AS FranchiseCode FROM #Table_A AS A LEFT OUTER JOIN #Table_B AS B ON A.FranchiseValue = CASE A.FranchiseGroup WHEN 'B1' THEN B.B1 WHEN 'B2' THEN B.B2 END

SELECT * FROM #Final_Table;

SELECT * FROM ( VALUES ('0001','aaaa'), ('0002','bbbb'), ('0003','Result1'), ('0003','Result2'), ('0004','ResultA'), ('0004','ResultB'), ('0005','dddd'), ('0006','eeee') ) AS X (ProductCode, FranchiseCode)

DROP TABLE #Table_A; DROP TABLE #Table_B; DROP TABLE #Final_Table;

You may need to change the CASE statement if you need to have other values involved.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ariel.gfernandez SSC Rookie Points: 43 More actions · Answer 2

I did it this way:

SELECT A.ProductCode, CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode ELSE A.FranchiseCode END AS FranchiseCode

FROM Table_A AS A LEFT JOIN Table_B AS B ON (A.FRANCHISEGROUP = 'B1' AND B.B1 = A.FRANCHISEVALUE ) OR (A.FRANCHISEGROUP = 'B2' AND B.B2 = A.FRANCHISEVALUE );

Vegeta7 Mr or Mrs. 500 Points: 572 More actions · Answer 3

You guys are amazing! Thank you both for the answers 😀 You made my day! I will look into your answers in more detail when I get home, but I quickly tested both and they are exactly what I needed. Thanks again sgmunson and Ariel Gonçalves Fernandez

aloshya Ten Centuries Points: 1105 More actions · Answer 4

ariel.gfernandez - Monday, August 28, 2017 8:25 AM
I did it this way:
SELECT A.ProductCode, CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode ELSE A.FranchiseCode END AS FranchiseCode
FROM Table_A AS A LEFT JOIN Table_B AS B ON (A.FRANCHISEGROUP = 'B1' AND B.B1 = A.FRANCHISEVALUE ) OR (A.FRANCHISEGROUP = 'B2' AND B.B2 = A.FRANCHISEVALUE );

how does the Case statement works in your query ,
basically i guess Here the case select values from table b if its empty

CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode

how does this part works.
(A.FRANCHISEGROUP = 'B1' ) OR (A.FRANCHISEGROUP = 'B2'

sgmunson SSC Guru Points: 110640 More actions · Answer 5

aloshya - Tuesday, August 29, 2017 5:39 AM
ariel.gfernandez - Monday, August 28, 2017 8:25 AM
I did it this way:
SELECT A.ProductCode, CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode ELSE A.FranchiseCode END AS FranchiseCode
FROM Table_A AS A LEFT JOIN Table_B AS B ON (A.FRANCHISEGROUP = 'B1' AND B.B1 = A.FRANCHISEVALUE ) OR (A.FRANCHISEGROUP = 'B2' AND B.B2 = A.FRANCHISEVALUE );
how does the Case statement works in your query ,
basically i guess Here the case select values from table b if its empty
CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode
how does this part works.
(A.FRANCHISEGROUP = 'B1' ) OR (A.FRANCHISEGROUP = 'B2'

CASE statements are a way to choose between multiple options. Look up the syntax in BooksOnLine. There are two basic ways to code a CASE statement. You can simply put CASE followed by any given expression, and then have as many WHEN value THEN value statements as you need to cover all the possible values of that expression that you're interested in, and possibly an ELSE statement to cover all other values, including NULL values if not already tested for. The other form of it is to put CASE immediately followed by as many WHEN condition THEN value statements as needed to cover all the conditions you want to test for, and possibly followed by an ELSE statement to cover for when none of the conditions you've specified are true. Does that help?

P.S. Important Note: You can only use CASE where an expression is expected.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

aloshya Ten Centuries Points: 1105 More actions · Answer 6

sgmunson - Tuesday, August 29, 2017 6:21 AM
aloshya - Tuesday, August 29, 2017 5:39 AM
ariel.gfernandez - Monday, August 28, 2017 8:25 AM
I did it this way:
SELECT A.ProductCode, CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode ELSE A.FranchiseCode END AS FranchiseCode
FROM Table_A AS A LEFT JOIN Table_B AS B ON (A.FRANCHISEGROUP = 'B1' AND B.B1 = A.FRANCHISEVALUE ) OR (A.FRANCHISEGROUP = 'B2' AND B.B2 = A.FRANCHISEVALUE );
how does the Case statement works in your query ,
basically i guess Here the case select values from table b if its empty
CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode
how does this part works.
(A.FRANCHISEGROUP = 'B1' ) OR (A.FRANCHISEGROUP = 'B2'
CASE statements are a way to choose between multiple options. Look up the syntax in BooksOnLine. There are two basic ways to code a CASE statement. You can simply put CASE followed by any given expression, and then have as many WHEN value THEN value statements as you need to cover all the possible values of that expression that you're interested in, and possibly an ELSE statement to cover all other values, including NULL values if not already tested for. The other form of it is to put CASE immediately followed by as many WHEN condition THEN value statements as needed to cover all the conditions you want to test for, and possibly followed by an ELSE statement to cover for when none of the conditions you've specified are true. Does that help?
P.S. Important Note: You can only use CASE where an expression is expected.

Great. I am going to save this.

ariel.gfernandez SSC Rookie Points: 43 More actions · Answer 7

aloshya - Tuesday, August 29, 2017 7:13 AM
sgmunson - Tuesday, August 29, 2017 6:21 AM
aloshya - Tuesday, August 29, 2017 5:39 AM
ariel.gfernandez - Monday, August 28, 2017 8:25 AM
I did it this way:
SELECT A.ProductCode, CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode ELSE A.FranchiseCode END AS FranchiseCode
FROM Table_A AS A LEFT JOIN Table_B AS B ON (A.FRANCHISEGROUP = 'B1' AND B.B1 = A.FRANCHISEVALUE ) OR (A.FRANCHISEGROUP = 'B2' AND B.B2 = A.FRANCHISEVALUE );
how does the Case statement works in your query ,
basically i guess Here the case select values from table b if its empty
CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode
how does this part works.
(A.FRANCHISEGROUP = 'B1' ) OR (A.FRANCHISEGROUP = 'B2'
CASE statements are a way to choose between multiple options. Look up the syntax in BooksOnLine. There are two basic ways to code a CASE statement. You can simply put CASE followed by any given expression, and then have as many WHEN value THEN value statements as you need to cover all the possible values of that expression that you're interested in, and possibly an ELSE statement to cover all other values, including NULL values if not already tested for. The other form of it is to put CASE immediately followed by as many WHEN condition THEN value statements as needed to cover all the conditions you want to test for, and possibly followed by an ELSE statement to cover for when none of the conditions you've specified are true. Does that help?
P.S. Important Note: You can only use CASE where an expression is expected.
Great. I am going to save this.

The ON clause:
(A.FRANCHISEGROUP = 'B1' AND B.B1 = A.FRANCHISEVALUE ) OR (A.FRANCHISEGROUP = 'B2' AND B.B2 = A.FRANCHISEVALUE );

Works this way:

If A.FRANCHISEGROUP = 'B1' then join by B.B1 = A.FRANCHISEVALUE
if A.FRANCHISEGROUP = 'B2' then join by B.B2 = A.FRANCHISEVALUE

aloshya Ten Centuries Points: 1105 More actions · Answer 8

ariel.gfernandez - Tuesday, August 29, 2017 7:18 AM
aloshya - Tuesday, August 29, 2017 7:13 AM
sgmunson - Tuesday, August 29, 2017 6:21 AM
aloshya - Tuesday, August 29, 2017 5:39 AM
ariel.gfernandez - Monday, August 28, 2017 8:25 AM
I did it this way:
SELECT A.ProductCode, CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode ELSE A.FranchiseCode END AS FranchiseCode
FROM Table_A AS A LEFT JOIN Table_B AS B ON (A.FRANCHISEGROUP = 'B1' AND B.B1 = A.FRANCHISEVALUE ) OR (A.FRANCHISEGROUP = 'B2' AND B.B2 = A.FRANCHISEVALUE );
how does the Case statement works in your query ,
basically i guess Here the case select values from table b if its empty
CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode
how does this part works.
(A.FRANCHISEGROUP = 'B1' ) OR (A.FRANCHISEGROUP = 'B2'
CASE statements are a way to choose between multiple options.   Look up the syntax in BooksOnLine. There are two basic ways to code a CASE statement. You can simply put CASE followed by any given expression, and then have as many WHEN value THEN value statements as you need to cover all the possible values of that expression that you're interested in, and possibly an ELSE statement to cover all other values, including NULL values if not already tested for.   The other form of it is to put CASE immediately followed by as many WHEN condition THEN value statements as needed to cover all the conditions you want to test for, and possibly followed by an ELSE statement to cover for when none of the conditions you've specified are true.   Does that help?
P.S. Important Note: You can only use CASE where an expression is expected.
Great. I am going to save this.
The ON clause:
(A.FRANCHISEGROUP = 'B1' AND B.B1 = A.FRANCHISEVALUE ) OR (A.FRANCHISEGROUP = 'B2' AND B.B2 = A.FRANCHISEVALUE );
Works this way:
If A.FRANCHISEGROUP = 'B1' then join by  B.B1 = A.FRANCHISEVALUE
if A.FRANCHISEGROUP = 'B2' then join by B.B2 = A.FRANCHISEVALUE

I added another record in
table A ( " B2' , Value 3 ,)
table B ( "Result C" "Result D" for value 3 )

The final result shows

why don't the product-code 4 and 7 were not similar here, even though the franchisegroup is same

sgmunson SSC Guru Points: 110640 More actions · Answer 9

Case Sensitve collation ?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Avi1 SSCrazy Points: 2323 More actions · Answer 10

aloshya - Tuesday, August 29, 2017 8:14 PM
ariel.gfernandez - Tuesday, August 29, 2017 7:18 AM
aloshya - Tuesday, August 29, 2017 7:13 AM
sgmunson - Tuesday, August 29, 2017 6:21 AM
aloshya - Tuesday, August 29, 2017 5:39 AM
ariel.gfernandez - Monday, August 28, 2017 8:25 AM
I did it this way:
SELECT A.ProductCode, CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode ELSE A.FranchiseCode END AS FranchiseCode
FROM Table_A AS A LEFT JOIN Table_B AS B ON (A.FRANCHISEGROUP = 'B1' AND B.B1 = A.FRANCHISEVALUE ) OR (A.FRANCHISEGROUP = 'B2' AND B.B2 = A.FRANCHISEVALUE );
how does the Case statement works in your query ,
basically i guess Here the case select values from table b if its empty
CASE A.FranchiseCode WHEN '' THEN B.FranchiseCode
how does this part works.
(A.FRANCHISEGROUP = 'B1' ) OR (A.FRANCHISEGROUP = 'B2'
CASE statements are a way to choose between multiple options.   Look up the syntax in BooksOnLine. There are two basic ways to code a CASE statement. You can simply put CASE followed by any given expression, and then have as many WHEN value THEN value statements as you need to cover all the possible values of that expression that you're interested in, and possibly an ELSE statement to cover all other values, including NULL values if not already tested for.   The other form of it is to put CASE immediately followed by as many WHEN condition THEN value statements as needed to cover all the conditions you want to test for, and possibly followed by an ELSE statement to cover for when none of the conditions you've specified are true.   Does that help?
P.S. Important Note: You can only use CASE where an expression is expected.
Great. I am going to save this.
The ON clause:
(A.FRANCHISEGROUP = 'B1' AND B.B1 = A.FRANCHISEVALUE ) OR (A.FRANCHISEGROUP = 'B2' AND B.B2 = A.FRANCHISEVALUE );
Works this way:
If A.FRANCHISEGROUP = 'B1' then join by  B.B1 = A.FRANCHISEVALUE
if A.FRANCHISEGROUP = 'B2' then join by B.B2 = A.FRANCHISEVALUE
I added another record in
table A ( " B2' , Value 3 ,)
table B ( "Result C" "Result D" for value 3 )

The final result shows

why don't the product-code 4 and 7 were not similar here, even though the franchisegroup is same

Franchise group is same for 4 an7 (B2) but the franchise value is different Value2 and Value3, so they are matching accordingly.

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 11

Hi ,
This might be answer your are looking for :

select distinct a.productcode,
case when a.franchisegroup='B1' and a.franchisevalue=b.B1 then b.FranchiseCode
when a.franchisegroup='B2' and a.franchisevalue=b.B2 then b.FranchiseCode
else a.FranchiseCode end as FranchiseCode
from #product a
left outer join #Franchise b
on (a.franchisevalue=b.b2
or a.franchisevalue=b.b1)

Cheers,
Saravanan

Saravanan