November 17, 2010 at 8:32 am
i have the below table with huge records in it
CREATE TABLE [dbo].[Main]
(
[varchar](10) NOT NULL,
[VALID_FROM] [datetime] NULL,
[VALID_TO] [datetime] NULL,
[NAME] [varchar](20) NULL,
[varchar](4) NULL,
[CENTER_TYPE] [varchar](10) NULL,
[CHARGE] [char](1) NULL,
)
and i have one more table which is populated with the records from other database.
Filer table script:
Create table tbl_Filer
(
CODE varchar(10),
CENTER varchar(4)
)
The sample data for the filer table is like below
Insert into tbl_Filer
select 'S001','A8192' union all
select 'S001','A5087' union all
select 'S001','A123' union all
select 'S001','A192' union all
select 'A003',Null union all
select 'A004',Null
Both the table will have code and center as comman between them
i need to filer the records from the Main Table and need to push the result set into another table, so basically i need the select statement. The Fileter criteria is like below
1) Filer all the Records from the main table where the the center value is null in the filter table
(like i don't need records From main table with code having 'A004','A003' as they have center as null)
2) i also need records from the main table which is having code as 'S001' and only those center which are there in the filter table.
is it possible write both the filter conditions in single select statement.
November 17, 2010 at 8:43 am
INSERT INTO ta1_Filter(CODE,CENTER)
SELECT CODE,CENTER from [dbo].[Main]
WHERE CENTER IS NOT Null
AND CODE like 'SC001'
I hope this is what you were looking for and it works.
November 17, 2010 at 8:44 am
select m.*
from Main m
join tbl_filter f
on f.code = m.code
and f.centre = m.centre
where m.code not in (select code from tbl_filter where center is null)
Cursors never.
DTS - only when needed and never to control.
November 17, 2010 at 8:56 am
INSERT INTO ta1_Filter(CODE,CENTER)
SELECT CODE,CENTER from [dbo].[Main]
WHERE CENTER IS NOT Null
AND CODE like 'SC001'
I hope this is what you were looking for and it works.
No, the second filer is not satisfied as the records with s001 code and center other than mentioned in the table will also include in your Query
SELECT CODE,CENTER from [dbo].[Main]
WHERE Code not in (select Code from the tbl_Filter where center is null )
union
SELECT CODE,CENTER from [dbo].[Main]
WHERE CODE = 'SC001' and CENTER in ( select center from tbl_filter where code is 'S001')
as i said the records are huge there will be a lot of common records from the both the select statements
i need a single Query instead of union
November 17, 2010 at 9:29 pm
You can use the EXISTS clause for the same..
SELECT*
FROMMain M
WHEREEXISTS
(
SELECT*
FROMtbl_Filer F
WHEREM.CODE = F.CODE AND M.CENTER = F.CENTER
)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply