SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get values from 2nd Table


Get values from 2nd Table

Author
Message
kk_tech
kk_tech
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 247
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
iamsql1
iamsql1
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 139
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.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7211 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
aaron.reese
aaron.reese
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 902
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
kk_tech
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 247
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
kk_tech
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 247
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search