locked
Need a query on this requirement RRS feed

  • Question

  • User-1279939347 posted

    I need a query on the below requirement.

    From the below picture, If give CompanyID 4569 and 4570

    First, it should search in FKA table. Then get the FKACompanyIDs List. That list should search in the TAGS table. I should get a Company name based on the CompanyIDs from the TAGS Table. Here I should exclude the CompanyIDs if any CompanyID having TagId 13.

    *Note: Here FKACompanyID also is a CompanyID

    The final result I should get the name of 4533 CompanyID name.

    Image

    It's an urgent requirement

    Tuesday, August 18, 2020 8:40 AM

Answers

  • User452040443 posted

    Try:

    select
        c.CompanyId,
        t.CompanyId as CompanyIdTag,
        ct.Name
    from Company as c
    inner join FKA as f
        on f.CompanyId = c.CompanyId
    inner join Company as ct
        on ct.CompanyId = f.FKACompanyId
    where
        not exists
            (select 1 from Tags as t
             where
                 t.CompanyId = f.FKACompanyId and
                 t.TagId = 13)
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 18, 2020 2:39 PM
  • User1535942433 posted

    Hi kishore309,

    As far as I think,you need to inner join company,FKA,TAGS tables.And then you need to exclude all companyid in TAGS which the tagid is same with 13.

    Just like this:

    select c.Name, TAGS.CompanyId,TAGS.TagId from company as c 
    inner join FKA as f
    on  c.CompanyId=f.CompanyId
    inner join TAGS
    on f.FKACompanyId=TAGS.CompanyId
    where not exists
            (select 1 from TAGS as t
             where
                 t.CompanyId = f.FKACompanyId and
                 t.TagId = 13)

    Result:

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 19, 2020 5:22 AM

All replies

  • User452040443 posted

    Hi,

    Try something like this:

    select distinct
        c.CompanyId,
        t.CompanyId as CompanyIdTag,
        ct.Name
    from Company as c
    inner join FKA as f
        on f.CompanyId = c.CompanyId
    inner join Tags as t
        on t.CompanyId = f.FKACompanyId
    inner join Company as ct
        on ct.CompanyId = f.FKACompanyId
    where
        t.TagId <> 13

    Hope this help

    Tuesday, August 18, 2020 1:20 PM
  • User-1279939347 posted

    Thanks for the reply!

    When I run the above query I got two records which is 4531 and 4533. But I should get only 4533 CompanyID.

    If Any CompanyID from TAGS table having 13 TagId it should not return that CompanyID

    Tuesday, August 18, 2020 1:56 PM
  • User452040443 posted

    Try:

    select
        c.CompanyId,
        t.CompanyId as CompanyIdTag,
        ct.Name
    from Company as c
    inner join FKA as f
        on f.CompanyId = c.CompanyId
    inner join Company as ct
        on ct.CompanyId = f.FKACompanyId
    where
        not exists
            (select 1 from Tags as t
             where
                 t.CompanyId = f.FKACompanyId and
                 t.TagId = 13)
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 18, 2020 2:39 PM
  • User1535942433 posted

    Hi kishore309,

    As far as I think,you need to inner join company,FKA,TAGS tables.And then you need to exclude all companyid in TAGS which the tagid is same with 13.

    Just like this:

    select c.Name, TAGS.CompanyId,TAGS.TagId from company as c 
    inner join FKA as f
    on  c.CompanyId=f.CompanyId
    inner join TAGS
    on f.FKACompanyId=TAGS.CompanyId
    where not exists
            (select 1 from TAGS as t
             where
                 t.CompanyId = f.FKACompanyId and
                 t.TagId = 13)

    Result:

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 19, 2020 5:22 AM
  • User-1279939347 posted

    Thank you yij sun!

    Wednesday, August 19, 2020 7:57 AM