Full join between two tables - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Full join between two tables

Author

Topic

narinder_malik2
Starting Member

2 Posts

Posted - 2012-05-04 : 08:36:22

I am new to this channel and have a requirement, not sure if the subject is absolutely correct

There are two tables:

TableA
======
A_id
A_name (not related to problem)

Sample:

A1 abc
A2 xyz
A3 mno
A4 pqr

TableB
======
A_id
Quarter
B_name (not related to problem)

Sample:

Q1 A1 hello
Q1 A2 welcome

Q2 A3 olleh
Q2 A4 122

Q3 A1 eisel
Q3 A3 iekas

I want to join these two tables, so that the resulting set should have all the values of A_id per Quarter column.

i.e.

Q1 A1 hello
Q1 A2 welcome
Q1 A3 -
Q1 A4 -

Q2 A3 olleh
Q2 A4 122
Q2 A1 -
Q2 A2 -

Q3 A1 eisel
Q3 A3 iekas
Q3 A2 -
Q3 A4 -

So basically the resulting set should have these green extra rows compared to the TAbleB

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-05-04 : 20:09:14

[CODE]declare @TableA table (
A_id varchar(10),
A_name varchar(50)
)

insert into @TableA
values
('A1', 'abc'),
('A2', 'xyz'),
('A3', 'mno'),
('A4', 'pqr')

declare @TableB table (
QtrName varchar(2),
A_id varchar(10),
B_name varchar(50)
)

insert into @TableB
values
('Q1', 'A1', 'hello'),
('Q1', 'A2', 'welcome'),

('Q2', 'A3', 'olleh'),
('Q2', 'A4', '122'),

('Q3', 'A1', 'eisel'),
('Q3', 'A3', 'iekas')

-------------------------------------------------

select
bu.QtrName,
au.A_id,
coalesce(b.B_name, '-') B_name
from
(
select distinct QtrName
from @TableB
) bu
cross join
(
select distinct A_id
from @TableA
) au
left outer join
@TableB b
on b.QtrName = bu.QtrName
and b.A_id = au.A_id
order by
bu.QtrName,
case
when coalesce(b.B_name, '-') = '-' then '1'
else '0'
end,
au.A_id[/CODE]

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

narinder_malik2
Starting Member

2 Posts

Posted - 2012-05-05 : 16:53:04

Thanks a ton!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources