CODEWITHSUNDEEP
phpmysql
Zurupupz
Zurupupz

Reputation: 345

MYSQL: Return ONLY matches with two tables with no conflicts

I want to know if this is possible to do with only one query.

You see, I have this table user_leads and I need to join this table two tables: lead_forms AND user_services on columns formid and serviceid respectively.

The result of this query should return ONLY rows that have a match in user_services OR lead_forms. DONT return rows without a match on at least one of these two.

Due to the nature of these two columns, both can be set and sometimes one will be set and the other NULL. And often there will be no match with either of the two tables.

So the problem is, if I make an inner join like this

FROM `user_leads`
INNER JOIN lead_forms ON lead_forms.formid = user_leads.formid

but then do 

INNER JOIN user_services ON user_services.serviceid = user_leads.serviceid

it will return all matches with lead_forms and ignore some matches with user_services when it should be returning them both. If I do the following though

FROM `user_leads`
WHERE user_leads.formid > 0 OR user_leads.serviceid > 0
LEFT OUTER JOIN user_services ON user_leads.serviceid = user_services.serviceid
LEFT OUTER JOIN lead_forms ON user_leads.serviceid = lead_forms.formid

It returns all matches from the two tables just like I wanted to, but since it is LEFT OUTER JOIN it ALSO returns rows from user_leads that have no match in user_services or lead_forms, causing errors.

I don't want to have to check for matches unless there really is no other way to do this with MYSQL. So if you know a correct way to do this your help is appreciated.

EDIT: For reference https://www.db-fiddle.com/f/pvjnJSAb913EX5Qc7c2QWo/1

This is what worked https://www.db-fiddle.com/f/5tgwy9AgBHx2dcqt9NoThN/2

Upvotes: 1

Views: 40

Answers (1)

forpas
forpas

Reputation: 164069

The LEFT joins will do what you want if you add a condition so that at least 1 of the joined columns is not null:

LEFT OUTER JOIN user_services ON user_leads.serviceid = user_services.serviceid
LEFT OUTER JOIN lead_forms ON user_leads.serviceid = lead_forms.formid
WHERE user_services.serviceid IS NOT NULL OR lead_forms.formid IS NOT NULL

Upvotes: 2

Related Questions