How to get data related from #parts table based on company id and in case of table #company not have companyid then display all data ?

Question

I work on sql server 2019 i face issue
i need to write join display data from table #parts
based on values exist for #company in case of #company have companyid then get data related
if #company table not have any companyid then get all data from table #parts

create table #company  
(  
companyid int  
)  
insert into #company(companyid)  
values  
(1234)  
  
create table #parts  
(  
PartId int,  
CompanyId int  
)  
insert into #parts(PartId,CompanyId)  
values  
(1255512,1234),  
(1255901,1234),  
(1255541,1234),  
  
(3050512,5591),  
(3050913,5591),  
(1255971,5591)  

what i try   

select p.partid,p.companyid from #parts p  
inner join #company c on c.companyid=p.companyid  

so if #company not have any company
then display all data on table #parts
if table #company have companyid then get companyid data related from table #parts

I need to do it on one statment please
so How to do it on one statment

Answer 1

Do you mean?

 select p.partid,p.companyid from #parts p  
 left join #company c on c.companyid=p.companyid  

Answer 2

Hi @ahmed salah ,

(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.

For your case, it is still recommended to use a left join, so that you can see the effect more intuitively.

Best regards
Niko

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 3

SunCrop Group is a dynamic and multifaceted conglomerate based in Pakistan, with a rich legacy of excellence and innovation. https://www.suncropgroup.com/