Need a query on this requirement[Answered] RSS

• • Reply
  kishore309

  Member

  25 Points

  118 Posts

  Need a query on this requirement

  Aug 18, 2020 08:40 AM|kishore309|LINK

  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

  Regards,
  Kishore Kumar
• • Reply
  imapsp

  Participant

  1130 Points

  299 Posts

  Re: Need a query on this requirement

  Aug 18, 2020 01:20 PM|imapsp|LINK

  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

  https://www.imoveisemexposicao.com.br/imoveis-venda-guarulhos-residencial-casa
• • Reply
  kishore309

  Member

  25 Points

  118 Posts

  Re: Need a query on this requirement

  Aug 18, 2020 01:56 PM|kishore309|LINK

  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

  Regards,
  Kishore Kumar
• • Reply
  imapsp

  Participant

  1130 Points

  299 Posts

  Re: Need a query on this requirement

  Aug 18, 2020 02:39 PM|imapsp|LINK

  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

  https://www.imoveisemexposicao.com.br/imoveis-venda-guarulhos-residencial-casa
• • Reply
  yij sun

  Contributor

  4050 Points

  1570 Posts

  Re: Need a query on this requirement

  Aug 19, 2020 05:22 AM|yij sun|LINK

  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

  ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
• • Reply
  kishore309

  Member

  25 Points

  118 Posts

  Re: Need a query on this requirement

  Aug 19, 2020 07:57 AM|kishore309|LINK

  Thank you yij sun!

  Regards,
  Kishore Kumar