Hi Josh,
As I mentioned elsewhere, you're very welcome, happy to help.
To answer your next questions as best I can (if I make any errors I'd be
grateful if someone else on the list would highlight them):
Let's imagine you have two tables, 1 table contains employees, the other
table contains the project to which each employee is currently assigned
(Note, this is not necessarily great database design, but it should
serve as an adequate illustration). In the real world, of course, not
every employee is currently assigned to a project, and not every project
has yet been assigned to an employee.
CREATE TABLE `tblemployees` (empid INT NOT NULL AUTO_INCREMENT, empname
VARCHAR(50) NOT NULL DEFAULT '', empdetails VARCHAR(50) NOT NULL DEFAULT
'', PRIMARY KEY (`empid`)) TYPE=MYISAM ROW_FORMAT=DEFAULT;
CREATE TABLE `tblprojects` (projid INT NOT NULL AUTO_INCREMENT, empid
INT NULL, projname VARCHAR(50) NOT NULL DEFAULT '', PRIMARY KEY
(`projid`)) TYPE=MYISAM ROW_FORMAT=DEFAULT;
INSERT INTO tblemployees (empname, empdetails) VALUES ('employee1',
'employee details1');
INSERT INTO tblemployees (empname, empdetails) VALUES ('employee2',
'employee details2');
INSERT INTO tblemployees (empname, empdetails) VALUES ('employee3',
'employee details3');
INSERT INTO tblemployees (empname, empdetails) VALUES ('employee4',
'employee details4');
INSERT INTO tblprojects (empid, projname) VALUES (1, 'project1');
INSERT INTO tblprojects (empid, projname) VALUES (NULL, 'project2');
INSERT INTO tblprojects (empid, projname) VALUES (2, 'project3');
INSERT INTO tblprojects (empid, projname) VALUES (4, 'project4');
So, if we wanted to do something relatively simple such as retrieve a
recordset that reflects those employees who have been assigned to a
project, and to which project they have been assigned, we would use an
INNER JOIN:
SELECT te.empname, te.empdetails, tp.projname FROM tblemployees te INNER
JOIN tblprojects tp ON te.empid = tp.empid;
This returns a recordset that looks like:
"employee1","employee details1","project1"
"employee2","employee details2","project3"
"employee4","employee details4","project4"
(Another way of retrieving the same recordset would be:
SELECT te.empname, te.empdetails, tp.projname FROM tblemployees te,
tblprojects tp WHERE te.empid=tp.empid;)
INNER JOINs only return records from tables where associated records can
be found in both.
Now, we can see from the above recordset that employee1 is assigned to
project1, employee2 is assigned to project3 and employee4 is assigned to
project4. This recordset doesn't communicate anything about employee3 or
project2, because the INNER JOIN excludes them for not having a matching
record in the other table.
Because we're dealing with a very small and very straightforward
scenario, however, we can probably work out under our own steam just by
looking at the above recordset that employee3 mustn't be currently
assigned to a project and project2 hasn't been assigned to an employee.
However, in a more complex scenario we might be mistakenly _assuming_
employee3 and project2 actually exist in our tables at all!
So that there's no potential for confusion, you can use, for example, a
LEFT JOIN to reflect all of the records in the tblemployees table and
only those records from tblprojects that are currently assigned to an
employee.
I.E.:
SELECT te.empname, te.empdetails, tp.projname FROM tblemployees te LEFT
JOIN tblprojects tp ON te.empid = tp.empid;
"employee1","employee details1","project1"
"employee2","employee details2","project3"
"employee3","employee details3",NULL
"employee4","employee details4","project4"
Here there can be no confusion -- employee3 does exist, but isn't
assigned to a project, as indicated by the NULL in the [projname]
column. Project2 is not listed in this recordset because the LEFT JOIN
in this example returns all of the records from tblemployees, but only
the matching records from tblprojects.
Similarly, we can return all of the projects and the details of the
employees that have been assigned to them, where they have been
assigned. To do this, change the syntax from a LEFT JOIN to a RIGHT
JOIN:
SELECT te.empname, te.empdetails, tp.projname FROM tblemployees te RIGHT
JOIN tblprojects tp ON te.empid = tp.empid;
"employee1","employee details1","project1"
NULL,NULL,"project2"
"employee2","employee details2","project3"
"employee4","employee details4","project4"
Here we see that project2 does exist, but no employee has yet been
assigned to it, as idicated by the NULLs in the [empname] and
[empdetails] columns. Employee3 is not listed in this recordset because
the RIGHT JOIN in this example returns all of the records from
tblprojects, but only the matching records from tblemployees.
You could, of course, simply maintain the LEFT JOIN syntax and change
the order of the tables in the FROM clause, thus:
SELECT te.empname, te.empdetails, tp.projname FROM tblprojects tp LEFT
JOIN tblemployees te ON te.empid = tp.empid;
By reversing the order of the tables the LEFT JOIN immediately above
returns exactly the same recordset as the RIGHT JOIN example above it.
So, the 'direction' of the join indicates which side of the tables
listed in the FROM clause will return all of their rows, and which side
will only return the data matched by the ON clause (and which can then
usually be further qualified / restrained by WHERE clauses).
In theory, it should be possible to retrieve a recordset that combines
the LEFT JOIN and RIGHT JOIN results from our examples above in a single
recordset.
This would probably look something like:
NULL,NULL,"project2"
"employee1","employee details1","project1"
"employee2","employee details2","project3"
"employee3","employee details3",NULL
"employee4","employee details4","project4"
This is usually performed by a FULL JOIN, but I am under the impression
that MySQL has yet to implement FULL JOINs (certainly, I can't get them
to work on ver 3.23.53 and the documentation for my version indicates
FULL JOINs are expected in ver 4.0 or 4.1).
Now, to go back to your original question, the people / events /
attendance example employed both a LEFT JOIN and a RIGHT JOIN because
the attendance table was acting as an intermediary between the people
and events tables. From my Microsoft Acess background, I would call this
a 'FIND UNMATCHED' query, although instead of finding the records in one
table that aren't reflected in another [1], we're finding and combining
the records from two tables that aren't associated with each other in a
third. I personally can't think of a way of representing the same query
you have developed but only using LEFT JOINs. I'm not in a position to
say it can't be done, but I certainly can't think of a way to do it.
All the best,
M Wells
[1] As a simple example of a 'find unmatched' query, if we had a table
that contained a record for each of the files on your computer and a
table of all of the files from your computer that you've backed up onto
a CD, then finding the records in your [allfiles] table that are
unmatched (i.e. don't exist) in your [backeduptocd] table should equate
to a list of all of the files on your computer that have yet to be
backed up to CD
-----Original Message-----
From: Josh L Bernardini [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 23 January 2003 5:24 AM
To: [EMAIL PROTECTED]
Subject: solution for opposite of this join / join from this subselect
thanks to brent, bob and M wells for their contributions to this
solution
and to m especially who seems to have put in a lot of time and nailed
it.
This query returns a list of people not attending a particular event,
given
an events table, a people table, and a many-many epeople table between
them. You can modify the where clause to show all the people attending a
particular event, all the events a person isn't/is attending - most of
what
you might need in most many - many relationships.
But I still haven't figured out the importance of left joins vs. right
joins. can anyone explain why this statement requires a right join to
work?
could it be rewritten to use a left join?
mysql> SELECT lastname, firstname, title, event
-> FROM people p
-> LEFT JOIN epeople ep on p.id = ep.pid
-> right join events e ON e.id = ep.eid
-> WHERE ep.pid IS NULL
-> and ep.eid is null
-> and e.id=2
-> ORDER BY e.id;
I reworte the statement like this and from doing so understand that, OK
epeople as the pivot point between the two tables needs to be on the
outside of the join - really placing it between the two tables. we're
looking for people where there is no record match for epeople.
SELECT lastname, firstname, title, event
FROM events e
LEFT JOIN epeople ep on e.id = ep.eid
right join people p ON p.id = ep.pid
WHERE ep.pid IS NULL
and e.id=2
ORDER BY e.id;
now whats the functional difference between this
SELECT lastname, firstname, title, event
FROM people p
LEFT JOIN epeople ep on p.id = ep.pid
right join events e on ep.eid = e.id
WHERE ep.pid IS NULL
and e.id = 2
ORDER BY ep.eid;
and this
SELECT lastname, firstname, title, event
FROM people p
LEFT JOIN epeople ep on p.id = ep.pid
right join events e on ep.eid = e.id
WHERE ep.pid IS NULL
and ep.eid=2
ORDER BY e.id;
as written the difference is in the and statements but in my result set
ep.eid == e.id == 2 so why can't you use the second statement
interchangably with the first?
thanks for any insights,
jb
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
