(beginner) JOIN after UNION - how? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

(beginner) JOIN after UNION - how?

Author

Topic

SwanAL
Starting Member

5 Posts

Posted - 2013-10-20 : 02:19:26

Hi,

if I first UNION two tables e.g. like:

select * from a

UNION

select * from b

and then I want to join two columns from table c, how can I do this?

Or do I have to join them BEFORE I union?

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 03:13:44

[code]
SELECT *
FROM
(
select * from a
UNION
select * from b
)t
INNER JOIN TableC c
ON c.Col = t.RelatedCol
[/code]

some points to note

1. the two queries in UNION should have same number of columns from a and b with datatypes of corresponding columns being compatible
2. The column by which a and b are related to TableC should be included in the UNION query referred by alias t
3. The above used method is called derived table ie UNION query with name t behaves like a table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwanAL
Starting Member

5 Posts

Posted - 2013-10-20 : 03:28:01

Thank you, I will try it

SwanAL
Starting Member

5 Posts

Posted - 2013-10-20 : 13:00:45

Thank you, it works fine, but I have another question.

I want to update the result table and I tried to reference is with:

update t
SET abc = 1
WHERE abc <> 1

but it does not find the result table as "t"

Msg 208, Level 16, State 1, Line 35
Invalid object name 't'.

Do you know why? And how can I update the result table?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 13:22:45

it should be as below


update TableName
SET abc = 1
WHERE abc <> 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwanAL
Starting Member

5 Posts

Posted - 2013-10-20 : 13:54:19

Hi,

yes, but what is the TableName?

The TableName after the "From" in the second line? Maybe my question want clear enough: I want to update the Table after the UNION and SELECT.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 06:37:43

you mean this?


UPDATE c
SET ABC = 1 
FROM
(
select * from a 
UNION
select * from b
)t
INNER JOIN TableC c
ON c.Col = t.RelatedCol
WHERE abc <> 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources