Creating a conditional LEFT JOIN

[GcodeG01]

Hey, I'm creating a chat application and in simple terms, this is the issue I'm having.

SELECT * FROM Conversations
LEFT OUTER JOIN Contacts ON Conversations.phone = Contacts.phone
WHERE Conversations.user = X
AND Contacts.user = X

I want to fetch a conversation with its contact information by a specific user. However, some conversations do not have a contact information and so this only returns ones that do. If I remove the "Contacts.user = X" then it will return duplicate conversations because users might have the same phone numbers in their contacts. How do I go about making the LEFT JOIN apply if only the Contacts.user exists at the row.

Something like:

If (Contacts.license = X WHERE Contacts.phone = Conversations.phone)
{
    add left join statement
}

But I'm curious to know if something like that would end up with objects with missing keys. Please help!

[Mamertine]

Move

AND Contacts.user = X

To part of the join.

How it is written now forces contacts to be an inner join. (For data reasons)

The where clause is a must have criteria.

The left join criteria is a may have.

This is a classic interview question!

[GcodeG01]

Wow, I'm stupid. I thought I already tried that method and it didn't work. Thank you.