Get values from 2nd Table

Question

Post reply

Get values from 2nd Table

kk_tech

Default port

Points: 1492
More actions
July 23, 2013 at 5:58 pm

#276232

Can someone show me how to combine these two tables and get one result set
CREATE TABLE_1
(
Field1 VARCHAR(4) NULL, Field2 VARCHAR(4) NULL, Field3 VARCHAR(4) NULL
)
INSERT INTO TABLE_1
VALUES(Field1, Field2, Field3)
(A, B, NULL),
(NULL, A, NULL),
(A, B, C),
(NULL, NULL, NULL),
CREATE TABLE_2
(
FieldName VARCHAR(4) NOT NULL, Title VARCHAR(20) NOT NULL)
INSERT INTO TABLE_2
(FieldName, Title)
Values (A, xyz),
(D, yyy)
(B, zzz)
Fieldname column data in TABLE_2 are matching in random column(Field1, Field2, and Field3) in TABLE_1
RESULT should shows Title of A or B etc... like
Field1, Field2, Field3
Title, Title, Title

Viewing 6 posts - 1 through 5 (of 5 total)

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

iamsql1 SSC Eights! Points: 817 More actions · Answer 1

Dear,

Use Union all operator to unified the result set.

Go to : http://www.w3schools.com/sql/sql_union.asp

Note. Datatype and no of columns should be same when you use union / union all,

Cheers

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

Let's start by correcting your DDL and sample data so that it will run in MS SQL (converted to temp tables to reduce clutter in my sandbox):

CREATE TABLE #TABLE_1

(

Field1 VARCHAR(4) NULL, Field2 VARCHAR(4) NULL, Field3 VARCHAR(4) NULL

);

INSERT INTO #TABLE_1 (Field1, Field2, Field3)

VALUES ('A', 'B', NULL),(NULL, 'A', NULL),('A', 'B', 'C'),(NULL, NULL, NULL);

CREATE TABLE #TABLE_2

(

FieldName VARCHAR(4) NOT NULL, Title VARCHAR(20) NOT NULL);

INSERT INTO #TABLE_2 (FieldName, Title)

Values ('A', 'xyz'),('D', 'yyy'),('B','zzz');

Next we'll apply the CROSS APPLY VALUES approach to UNPIVOT from #TABLE_1, eliminating NULLs and then doing a JOIN to #TABLE_2.

SELECT b.Field, c.Title

FROM #TABLE_1 a

CROSS APPLY (

VALUES (Field1),(Field2),(Field3)) b (Field)

INNER JOIN #Table_2 c ON b.Field = c.FieldName

WHERE Field IS NOT NULL;

Go

DROP TABLE #Table_1;

DROP TABLE #Table_2;

The CAV approach to UNPIVOT is explained in the first of my signature links below.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

aaron.reese SSChampion Points: 13498 More actions · Answer 3

You have an answer to the original question but I would like to point out that it smacks of poor table design. Why do you have values in a child table that are related to more than one column in the parent table. What is the real world application of this structure, we may be able to suggest a more suitable database layout that avoids the pain and confusion of CROSS APPLY or UNPIVOT

kk_tech Default port Points: 1492 More actions · Answer 4

These table are not from production database, this is how table are created to hold data, so I am trying to clean and combine data from both tables.

kk_tech Default port Points: 1492 More actions · Answer 5

I was able to get the result based on your answer, but first I had to use OUTER APPLY for FIELD1 and then CROSS APPLY on every other fields (FIELD2, FIELD3, FIELD4 and FIELD5). If I don't use OUTER APPLY then I don't get result for some reason. It was very helpful, but I still need to understand myself.