Join on tables with OR (repeating data) - SQL Server 2005

Firstly to put things into context I am trying to write a search on SQL Server 200开发者_如何学运维5. Below is my table structure

Schema1.Table1
GUID 
1
2
3

Schema2.Table2
GUID MAINTITLE
1    Water Monkies
2    Water Doggies  

Schema3.Table3
GUID MAINTITLE
3    Water Hyrdas

Expected behavior is that the user will search for 'Water' and I have to retrieve all the GUID in Schema1.Table1 match them with entries in Schema2.Table2 and Schema3.Table3 where GUID in the list AND MAINTITLE like '%WATER%'

I have to achieve this using JOINS.

What I've done so far is:

select Schema1.Table1.GUID
from  Schema1.Table1 JOIN Schema2.Table2 ON Schema1.Table1.GUID = Schema2.Table2.GUID
JOIN Schema3.Table3 ON Schema1.Table1.GUID = Schema3.Table3.GUID

but this returns an AND'ed result which gives me no results

I then tried

select distinct Schema1.Table1.GUID
from Schema1.Table1, Schema2.Table2, Schema3.Table3
where (Schema2.Table2.GUID=Schema1.Table1.GUID OR Schema3.Table3.GUID=Schema1.Table1.GUID 
) AND (Schema2.Table2.MAINTITLE like '%water%' OR Schema3.Table3.MAINTITLE like '%water%')

but since this is an implied join it returns all rows of table2 where table3s' maintitle is like water too.

Can I haz some help plese?

it's not possible to do that with joins, you have to use unions. although i can't provide proof of this

select T1.GUID
from T1 join (T2 union all T3) as T on T1.GUID=T.GUID
where T.MAINTITLE like '%WATER%'

maybe

You can try using a LEFT JOIN and ISNULL

select  Schema1.Table1.GUID,
        ISNULL(Schema2.MAINTITLE, Schema3.MAINTITLE)
from    Schema1.Table1 LEFT JOIN
        Schema2.Table2  ON  Schema1.Table1.GUID = Schema2.Table2.GUID LEFT JOIN 
        Schema3.Table3 ON Schema1.Table1.GUID = Schema3.Table3.GUID
WHERE   ISNULL(Schema2.MAINTITLE, Schema3.MAINTITLE) LIKE '%blabla%'

A UNION ALL does seem like a better option, but this can work too.

Also have a look at using COALESCE (Transact-SQL) instead of ISNULL