Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Newbie T-SQL ? Using Joins Expand / Collapse
Author
Message
Posted Saturday, May 25, 2013 6:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:24 PM
Points: 3, Visits: 34
Hello all, this my first time posting,but I have read several of the killer articles from the site. I am a newbie to T-SQL. I am trying to do the following. I have tried left outer join and using a subquery..results very from duplicates to an empty table.

Table1

Column1 Column2
A 1
B 1
C 1
A 2
B 2
C 2
D 2


Table 2

Column1
A
B
C
D

Output Table should look like as I am only interest in values that are in T2 that are not in T1 any give value of T1C2

Column1 Column2
D 1

Any light will be appreciated greatly
Post #1456824
Posted Saturday, May 25, 2013 10:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:11 PM
Points: 20,861, Visits: 32,887
You are looking at something like this:


declare @Table1 table(
Col1 char(1),
Col2 int
);

declare @Table2 table(
Col1 char(1)
);

insert into @Table1
values ('A',1),
('B',1),
('C',1),
('A',2),
('B',2),
('C',2),
('D',2);

insert into @Table2
values ('A'),('B'),('C'),('D');

select * from @Table1;
select * from @Table2;

with UniqCol2 as (
select distinct Col2 from @Table1
), ExcludedData as (
select
t2.Col1,
uc.Col2
from
@Table2 t2
cross apply UniqCol2 uc
except
select
t1.Col1,
t1.Col2
from
@Table1 t1)
select Col1 from ExcludedData;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1456837
Posted Saturday, June 1, 2013 3:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:24 PM
Points: 3, Visits: 34
Thanks that is excactly what I was looking for. I am not familiar with cross apply,but look forward to using it.
Cheers!
Post #1458973
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse