CODEWITHSUNDEEP
mysql
Hanzo
Hanzo

Reputation: 1899

Multiple search with join

I'm trying to search a rows of table3 that have id match in a table1 OR table2 with this query

SELECT T1.*, T3.*,T2.*
FROM (
    select id
    from table1
    where condition like '%field%') T1
inner join table3 T3
ON T3.id=T1.id
left join (
    select id
    from table2
    where condition like '%field%') T2
ON T3.id=T2.id

If in table T1 have matches but in table2 not, the query works fine but if in table1 not have matches but have in table2 the query not show any results.

Someone can help me?

Thanks

Upvotes: 1

Views: 81

Answers (1)

AdrianBR
AdrianBR

Reputation: 2588

do left join to avoid excluding rows, and add a where with the condition you need FOr readability sake,avoid right joins and jsut use proper table order

SELECT T1.*, T3.*,T2.*
FROM Table3 T3 

left join (
    select id
    from table1
    where condition like '%field%') T1   
ON T3.id=T1.id

left join (
    select id
    from table2
    where condition like '%field%') T2
ON T3.id=T2.id

where t3.id is not null or t2.id is not null

Upvotes: 2

Related Questions