Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Filter data in table with other table

Discussion in 'Information Technology' started by agent, Jul 28, 2006.

  1. agent

    agent
    Expand Collapse
    Guest

    Hi,

    I'm trying to filter data in a certain table using another table. The first
    one has about 290000 records and all text fields, the second 760000 and all
    number fields. Every company has a specific number. The records in first
    table all have a unique code and represent all companies in a certain market.

    The second table holds all companies that are client of a certain company.
    I'd like to keep all companies in the first table that are a client of this
    company. So I need to filter out all companies in the first table that do not
    appear in the second table.

    How can this be done?

    thx
     
  2. Loading...


  3. ChrisM

    ChrisM
    Expand Collapse
    Guest

    How about this:

    SELECT * from Table1
    WHERE EXISTS
    (
    SELECT * FROM Table2
    WHERE Table2.CustomerId = Table1.CustomerId
    )

    ??

    Cheers,

    Chris.


    "agent" <agent@discussions.microsoft.com> wrote in message
    news:7E1B4166-B5E8-453B-AF39-825E4BA1F843@microsoft.com...
    > Hi,
    >
    > I'm trying to filter data in a certain table using another table. The
    > first
    > one has about 290000 records and all text fields, the second 760000 and
    > all
    > number fields. Every company has a specific number. The records in first
    > table all have a unique code and represent all companies in a certain
    > market.
    >
    > The second table holds all companies that are client of a certain company.
    > I'd like to keep all companies in the first table that are a client of
    > this
    > company. So I need to filter out all companies in the first table that do
    > not
    > appear in the second table.
    >
    > How can this be done?
    >
    > thx
    >
    >
    >
    >
     
  4. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    I may have missed something. Your description sounds like one table has
    only text fields and the other has only numeric fields. If this is true,
    you don't have a field in common between the two tables.

    How do you propose to "connect" them?

    --
    Regards

    Jeff Boyce
    Microsoft Office/Access MVP
    http://mvp.support.microsoft.com/

    Microsoft IT Academy Program Mentor
    http://microsoftitacademy.com/

    Microsoft Registered Partner
    https://partner.microsoft.com/


    "agent" <agent@discussions.microsoft.com> wrote in message
    news:7E1B4166-B5E8-453B-AF39-825E4BA1F843@microsoft.com...
    > Hi,
    >
    > I'm trying to filter data in a certain table using another table. The

    first
    > one has about 290000 records and all text fields, the second 760000 and

    all
    > number fields. Every company has a specific number. The records in first
    > table all have a unique code and represent all companies in a certain

    market.
    >
    > The second table holds all companies that are client of a certain company.
    > I'd like to keep all companies in the first table that are a client of

    this
    > company. So I need to filter out all companies in the first table that do

    not
    > appear in the second table.
    >
    > How can this be done?
    >
    > thx
    >
    >
    >
    >
     
  5. ChrisM

    ChrisM
    Expand Collapse
    Guest

    btw,

    You don't have all the company data (Name, Address...(?) in both tables do
    you?
    If so, you might want to consider re-thinking your DB layout a bit. Repeated
    data is a bit of a nono in DB design.

    Cheers,

    Chris.


    "agent" <agent@discussions.microsoft.com> wrote in message
    news:7E1B4166-B5E8-453B-AF39-825E4BA1F843@microsoft.com...
    > Hi,
    >
    > I'm trying to filter data in a certain table using another table. The
    > first
    > one has about 290000 records and all text fields, the second 760000 and
    > all
    > number fields. Every company has a specific number. The records in first
    > table all have a unique code and represent all companies in a certain
    > market.
    >
    > The second table holds all companies that are client of a certain company.
    > I'd like to keep all companies in the first table that are a client of
    > this
    > company. So I need to filter out all companies in the first table that do
    > not
    > appear in the second table.
    >
    > How can this be done?
    >
    > thx
    >
    >
    >
    >
     

Share This Page