Using OR with JOIN versus multiple searches? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Using OR with JOIN versus multiple searches?

Author

Topic

fwdtech
Starting Member

11 Posts

Posted - 2014-08-01 : 08:19:59

A project I am on uses a stored procedure to query a task table with an inner join on a clients table to get the clients name like:

select task.name from tasks t inner join clients c on t.clientid = c.clientid

The dev team decided to add tasks that have no client associated by inserting a zero in the clientid field so now the procedure fails to find any result as there is no clientid=0 in the clients table. I tried an OR on the join like "inner join clients c on (t.clientid = c.clientid) OR t.clientid=0" but that results in a row being returned for every client record and overall just seems a terrible way to go about that.

Is there an easier way other than retooling the whole tasks system to query for either a match (client name) or a 0 with only the one record being matched?

My only alternative right now is to create a new stored procedure that queries just clientid=0 and returning the appropriate data or changing the stored proc to run two queries and union the two results.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-01 : 08:41:32

So you are trying to bring back all tasks where the clients are in both the task table and client table as well as task they have a client of 0

This will bring back all tasks and only those clients that are equal. So if you only have tasks with clients and task with 0 for a client - you should get the correct result.

select task.name from tasks t left join clients c on t.clientid = c.clientid

If you have clients that do not have tasks - they will not be returned.
If you have tasks with no clients, such as client = 0 - all will be returned

fwdtech
Starting Member

11 Posts

Posted - 2014-08-01 : 08:57:30

quote:
Originally posted by MichaelJSQL

So you are trying to bring back all tasks where the clients are in both the task table and client table as well as task they have a client of 0

The query is on the tasks table (short version of it) [id,subject,task,clientid] and clients table (short) [id,clientid,name].

A tasks is inserted with clientid from clients table so "select t.subject,t.task,c.name from tasks t inner join clients c on t.clientid=c.clientid" gets the right results.

However the devs added tasks not associated with a client - a "general" task with a client ID of 0. Since there is no client record with an ID of 0, the join will never return that row.

Tasks
1 | 'Test' | 'Test Task' | 235411
2 | 'Test2' | 'Test Task2' | 118643
3 | 'Test3' | 'Test Task3' | 0

Clients
1 | 235411 | 'Mike'
2 | 118643 | 'Jim'

There is no way (that I can see) to get row 3 returned from an inner join from clients even if the "name" returned is NULL - which is what I was gunning for so I created two stored procedures for now and I union the results when I process the query on the server.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-08-01 : 10:14:41

Use a LEFT join - otherwise you would get only clientids that exist in both tables - and use the appropriate where clause - e.g.

SELECT  t.subject ,
        t.task ,
        c.name
FROM    tasks t
        LEFT JOIN clients c ON t.clientid = c.clientid
WHERE
	c.clientid IS NOT NULL OR t.clientid = 0;

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-01 : 10:22:55

If you left join from tasks that would not be an issue

All tasks that have a client in the client table and the task table as well as those that have a client = 0 in the task table: no where clause needed.

select task.name from tasks t left join clients c on t.clientid = c.clientid

fwdtech
Starting Member

11 Posts

Posted - 2014-08-01 : 10:24:37

quote:
Originally posted by James K

Use a LEFT join - otherwise you would get only clientids that exist in both tables - and use the appropriate where clause - e.g.

Bingo - I sword I had tried that but clearly had not gotten it right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources