Outer Join?

[Andrew Bones Simpson]

Hi, I'm fairly new to using SQL, and I have a problem. I have a large
query that is getting lots of data. Its working fine, but I need to
add one part to it. Basically, I need to add this:

SELECT fname, lname FROM request, request_analyst, analyst, employee,
person
WHERE request.isrf_num=request_analyst.isrf_num AND
request_analyst.analyst_id=analyst.analyst_id AND
analyst.employee_id=employee.employee_id AND
employee.person_id=person.person_id

That is just what I need to add to the query. There is other stuff in
there, but it would be too complicated to post it here.

I'm pretty sure that what I wrote above is called an inner join. As
you might be able to see, "requests" can have multiple "analysts", and
the request_analyst table links them, and the query just gets the
analyst name by going through a bunch of tables. The problem is, this
will only return requests that HAVE an analyst associated with them.
I need to get the requests regardless of whether the requests have an
analyst. So, I think I need an outer join, and I know how to do this
for just two tables:

SELECT ... FROM request RIGHT OUTER JOIN request_analyst ON
request.isrf_num=analyst.isrf_num

But I have no idea how to do it for so many tables, as in the first
piece of code I posted. I hope I didn't make this too confusing. I'm
using mySQL. Can anyone give some help?

Thank you,
Andrew

 

[John Viescas]

Like this:

SELECT fname, lname
FROM request LEFT JOIN
(((request_analyst INNER JOIN analyst
ON request_analyst.analyst_id = analyst.analyst_id)
INNER JOIN employee
ON analyst.employee_id = employee.employee_id)
INNER JOIN person
ON employee.person_id = person.person_id)
ON request.isrf_num = request_analyst.isrf_num

What tables contain fname and lname? You should qualify those. Also, you
should probably output at least one column from request - if you have
requests that have not assigned analyst, you'll get Null rows in the output,
but you won't have a clue what requests have the problem.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411