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: [email protected]
> 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
>
