August 14, 2014 at 11:10 am
Hi,
I am having two tables as below:
I want to display records from @table1 only when combination of col2,col3 and col4 are present in @table2.
In Below case I want output as: below two records only. Kindly help.
'test1', 'need this record', 25, {d '1901-01-01'}
'test3', 'some longer value', 23, {d '1900-01-01'}
declare @table1 table (
col1 varchar(10) not null,
col2 varchar(200) null,
col3 int not null,
col4 datetime null
);
declare @table2 table (
col1 varchar(10) not null,
col2 varchar(200) null,
col3 int not null,
col4 datetime null
);
insert @table1( col1, col2, col3, col4)
select 'test1', 'need this record', 25, {d '1901-01-01'} union all
select 'test2', 'some longer value', 21, {d '1901-01-01'} union all
select 'test3', 'some longer value', 23, {d '1900-01-01'}
insert @table2( col1, col2, col3, col4)
select 'test1', 'need this record', 25, {d '1901-01-01'} union all
select 'test21', 'Need this record', 25, {d '1900-01-02'} union all
select 'test4', 'another longer value', 22, {d '1900-01-01'} union all
select 'test3', 'some longer value', 23, {d '1900-01-01'}
Kindly help.....
Thanks,
Abhas
August 14, 2014 at 11:23 am
select * from @table1 t1
join @table2 t2
on t1.col2 = t2.col2 and t1.col3 = t2.col3 and t1.col4 =t2.col4
August 15, 2014 at 10:17 am
If you only want to output values from table1, then try:
SELECT *
FROM @table1 t1
WHERE EXISTS
(
SELECT 1
FROM @table2 t2
WHERE t1.col2 = t2.col2
AND t1.col3 = t2.col3
AND t1.col4 = t2.col4
)
- Les
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply