OK, I don't see much wrong with this version either. Again I ask you: Which version you are using? Can you post some sample data and the incorrect results?
Here is a similar query that is working for me. This matches Projects with the people who have the resources to complete them (Suppliers). (If this data looks familiar to some readers, I also used these tables in an earlier thread): CREATE TABLE people ( name varchar(11) default NULL, rsrc varchar(15) default NULL ); INSERT INTO people VALUES ('noah','wood'),('noah','canvas'),('lincoln','wood'),('davinci','canvas'),('davinci','paint'); CREATE TABLE project ( proj varchar(11) default NULL, rsrc varchar(15) default NULL ); INSERT INTO project VALUES ('ark','wood'),('ark','canvas'),('cabin','wood'),('monalisa','canvas'),('monalisa','paint'),('jeans','canvas'),('jeans','sewingmachine'); select * from people; +---------+--------+ | name | rsrc | +---------+--------+ | noah | wood | | noah | canvas | | lincoln | wood | | davinci | canvas | | davinci | paint | +---------+--------+ 5 rows in set (0.06 sec) select * from project; +----------+---------------+ | proj | rsrc | +----------+---------------+ | ark | wood | | ark | canvas | | cabin | wood | | monalisa | canvas | | monalisa | paint | | jeans | canvas | | jeans | sewingmachine | +----------+---------------+ 7 rows in set (0.05 sec) SELECT pr.proj, pr.rsrc, p.name FROM project pr LEFT JOIN people p ON p.rsrc = pr.rsrc; +----------+---------------+---------+ | proj | rsrc | name | +----------+---------------+---------+ | ark | wood | noah | | ark | wood | lincoln | | ark | canvas | noah | | ark | canvas | davinci | | cabin | wood | noah | | cabin | wood | lincoln | | monalisa | canvas | noah | | monalisa | canvas | davinci | | monalisa | paint | davinci | | jeans | canvas | noah | | jeans | canvas | davinci | | jeans | sewingmachine | NULL | +----------+---------------+---------+ 12 rows in set (0.00 sec) I get a NULL for person on the last line because nobody has a sewing machine to share for the "jeans" project. To match your second query, I need to detect either a particular person ( I pick 'davinci') or a NULL. SELECT pr.proj, pr.rsrc, p.name FROM project pr LEFT JOIN people p ON p.rsrc = pr.rsrc WHERE p.name='davinci' or p.name is null; +----------+---------------+---------+ | proj | rsrc | name | +----------+---------------+---------+ | ark | canvas | davinci | | monalisa | canvas | davinci | | monalisa | paint | davinci | | jeans | canvas | davinci | | jeans | sewingmachine | NULL | +----------+---------------+---------+ 5 rows in set (0.00 sec) Which is what we expect. Now, can you please show us what is wrong with your output? Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Rich Brant" <[EMAIL PROTECTED]> wrote on 04/19/2005 03:46:33 PM: > I forgot the important part: what I want is to filter on a userID in the > person table such as - > > SELECT u.Username, p.UserID > FROM Users u LEFT OUTER JOIN > Person p ON u.UserID = p.UserID > WHERE (p.UserID = 5) OR > (p.UserID IS NULL) > > THis will return both the matching recs from the user table and NULLs from > the person table in sql server, but is what I cant get to work in mysql... > > > > > > > > _____ > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 19, 2005 3:14 PM > To: Rich Brant > Cc: mysql@lists.mysql.com > Subject: Re: mysql syntax > > > > > Rich Brant <[EMAIL PROTECTED]> wrote on 04/19/2005 03:05:51 PM: > > > I'm used to doing something simple such as the following in sql server: > > > > SELECT u.Username, p.UserID > > FROM Users u LEFT JOIN > > Person p ON u.UserID = p.UserID > > > > However, I'm not seeing the same results in MySQL. I don't get all > > the recs in the users table and NULLs in the userID column from the > > person table when the users.userID is not in the person table. I get > > no recs at all. What am I missing here? > > > > Thanks! > > > I don't see any obvious problems with your query. I use LEFT JOINs all the > time. > > Which version MySQL are you using? Can you give us an example of what your > base data looks like (Users table and Person table), and what you actually > got as a response? > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine >