Help in SQL

  • 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.

  • 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.

  • 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.

  • 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

  • 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

    )


    Kingston Dhasian

    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