Condition Based Join in SQL Server

I have the following tables

1. Invoice
2. Organization
3. Customer

I’m trying to conditionally join these tables by the following condition: If PTypecd = ‘I’ then data come from the Customer table And if Ptypecd = ‘O’ then data come from the Organization table into the Invoice table.

the query i tried so far:

Select 
    I.PCD, I.PtypeCD,    
From 
    Invoice I 
Left Join 
    Customer C ON I.PCD = C.CustomerCD 
Left Join 
    Organization O ON I>PDC = O.Organization  

How does this condition be used in Join?

Answer

Here’s a query that joins two tables conditionally, in your case you need to join the Customer table when the PTypecd of Invoice is 1 so add another condition for joint statement AND I.PTypecd = 1, and it the other join you need to check wither I.PTypecd = 0 and that will let you join the other table Organization

SELECT I.PCD, I.PtypeCD
FROM Invoice I
LEFT JOIN Customer a ON a.CustomerCD = i.PDC AND I.PTypecd = 1
LEFT JOIN Organization b ON b.Organization = i.PDC AND i.PTypecd = 0;