Problems with stored procedure (SQL). How to not use a field when empty?

[PowerTegga]

Hi.

I've got a stored procedure that looks like this:

Select

proj.[NO_]
,resPro.Name AS ProjectResponsible
,resSale.Name AS SalesResponsible
,resInst.Name AS InstallerResponsible

FROM [XXXXX$Job] as proj,
XXXXX$Customer as cust,
XXXXX$Resource as resPro,
XXXXXr$Resource as resSale,
XXXXX$Resource as resInst

WHERE

proj.No_ like (gets a value in Power Automate)
and
resPro.No_ = proj.[Project Responsible]
and
resSale.No_ = proj.[Sales Responsible]
and
resInst.No_ = proj.[Installer Responsible]

This is no problem when proj.[Project Responsible],  proj.[Sales Responsible] and proj.[Installer Responsible] have an integer. The problem is when proj.[Installer Responsible] is empty,  this will return no result.

I guess I need a way that exclude 

1.  resInst.Name AS InstallerResponsible from SELECT

2.  XXXXX$Resource as resInst from FROM

3.  and resInst.No_ = proj.[Installer Responsible] from WHERE

I've tried resInst.No_ = proj.[Installer Responsible] OR Coalesce(proj.[Installer Responsible],'') = '' etc, but that just gives me many results etc. I guess I need to remove it from the FROM also to get the correct result?

Anyone got a clue what to do?

[manuelstgomes]

HI @PowerTegga 

In your query, I can't see a join with the customer column. Also if proj.No is the primary key you should join by = and not by "like".

If you add an inner join with the customer column I think it will solve your issue

Can you please check if and let me know if you have any questions?

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[PowerTegga · Accepted solution]

Hi, @manuelstgomes and thanks for your reply.

I've solved it by using INNER JOIN, so it now looks like this:

Select

proj.[NO_]
,resPro.Name AS ProjectResponsible
,resSale.Name AS SalesResponsible
,resInst.Name AS InstallerResponsible

FROM [XXXXX$Job] as proj,
           LEFT JOIN XXXXX$Customer as cust ON [foreign key to customer here]
           LEFT JOIN XXXXX$Resource as resPro ON resPro.No_ = proj.[Project Responsible]
           LEFT JOIN XXXXXr$Resource as resSale ON resSale.No_ = proj.[Sales Responsible]
           LEFT JOIN XXXXX$Resource as resInst ON resInst.No_ = proj.[Installer Responsible]

WHERE

proj.No_ = (gets a value in Power Automate)