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


1    Water Monkies
2    Water Doggies  

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

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

Need Your Help

Mule - Returning in JSON and XML on runtime..?

xml json dynamic mule

In Mule, currently I am using a custom transformer "JavaObjectToJSON" (as a Response Transformer) to convert a POJO to JSON i.e. my component class returns a Java Object (List or HashMap) and this

Check if a webservice exists .net web-services .net-4.0 asmx

Could someone please be kind enough to show me the best way to determine if a webservice (ASP.NET) exists at a given URL?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.