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 2005. 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%'
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