Need a query on this requirement

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.

It's an urgent requirement

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

User-1279939347 posted

Thank you yij sun!