Select rows from one table, and partial from joining table LEFT OUTER

[fcoomermd]

Here is what I would like to be able to do
I have the following tables:
#1 Employees
id
firstName
lastName

#Tasks
id
employeeID
description
flag


I want to Join the tables, and return the employee result from employee, regardless if there are joining rows in the Tasks table. So therefore I will be using a Left Outer Join, like this:
SELECT employees.firstName, employees.lastName, Tasks.*
FROM employees LEFT OUTER JOIN
tasks ON employees.id = tasks.emplyeeID
WHERE (employee.id = 119)

***HOWEVER***, this is the tricky part. I want to narrow the returned Tasks to join on. I would also like to say, where Tasks.flag=1. But if there are no relating rows in the Tasks table, regardless of the Left Outer Join, nothing is returned.

Please, any help would be appreciated. I have been fighting over this one for awhile.
Thanks
FC

 

[tc3596]

try this...

WHERE (employee.id = 119) and
(1 = Case When tasks.flag is null or tasks.flag = 1 then 1 End)

Therefore, all records with flag set to null or flag = 1 are returned. You may also want to trap for an empty string. What I like to do is...

isnull(rtrim(tasks.flag), '') = ''

this actually checks for nulls and/or empty strings and would replace the above "tasks.flag is null" statement

 

[AngelB]

If you place criteria on the right hand table in a LEFT OUTER JOIN, in effectively negates the join and makes it an INNER JOIN instead. Try including the criteria in the join syntax itself e.g.

SELECT employees.firstName, employees.lastName, Tasks.*
FROM employees LEFT OUTER JOIN tasks
ON employees.id = tasks.employeeID
AND employees.id = 119
AND Tasks.flag = 1

Should do the trick

Gez

 

[fcoomermd]

I have a table person, and a table called cars
I would like to set up a query that joins the table ex.

"select p.firstname, p.lastname, c.carType from person p, cars c where p.id=c.pid"

the tables are obviously 1 to many

I would like the returned result to look like this

Bob, Jones, Porche, Buick, Ferrrari
Dan Smith Chevy, Toyota, Honda, Suzukit, GMC

How can this be done... Spent too much time, and I am no closer... Any help would be apprciated.
Thanks
FC