Using Outer Join

[neboat]

Hi,

I am having problems with outer join. All of the examples on Outer Join I see on the web deals with joining only two tables. What if I am joining multiple tables in addition to outer join? Please see the SQL statement below. I get an error when I try to run it.... pls help, thanks in advance. (using MS Access currently)

"SELECT *
FROM customer, company, priority, problem_area, status, ticket
LEFT OUTER JOIN ticket_assign
ON (ticket.ticket_id=ticket_assign.ticket_id)
WHERE (ticket.customer_id=customer.customer_id)
AND (customer.company_id=company.company_id)
AND (ticket.status_id=status.status_id)
AND (ticket.priority_id=priority.priority_id)
AND (ticket.problem_area_id=problem_area.problem_area_id)
AND (ticket.ticket_id=ticket_assign.ticket_id)
AND NOT (ticket.status_id=6)

[ZydoSEO]

Without knowing your schema and since your joins were in no particular order, I tried to figure it out in reverse. I would suggest getting used to doing joins as I wrote it below. Once you get the hang of it, it's so much easier and clearer than the old style joins (SELECT * FROM t1,t2,t3...). I fought it for a long time in favor of old style joins but the newer style ANSI joins are so much easier to understand.

Give this a try and see what you get:

SELECT *
FROM customer cu
JOIN company co ON cu.company_id=co.company_id
JOIN ticket t ON cu.customer_id=t.customer_id
JOIN priority pri ON t.priority_id=pri.priority_id
JOIN problem_area pa ON t.problem_area_id=pa.problem_area_id
JOIN status s ON t.status_id=s.status_id
LEFT OUTER JOIN ticket_assign ta ON t.ticket_id=ta.ticket_id
WHERE ticket.status_id <> 6

Hope it helps.

[edited by: ZydoSEO at 5:29 am (utc) on Jan. 16, 2008]

[neboat]

Thank you for your help ZydoSEO, much appreciated. I tried your code but it wasn't working for me, so I played around with it and finally got the code to work correct:

SELECT *
FROM ((((((ticket
INNER JOIN customer
ON customer.customer_id=ticket.customer_id)
INNER JOIN company
ON company.company_id=customer.company_id)
INNER JOIN priority
ON priority.priority_id=ticket.priority_id)
INNER JOIN problem_area
ON problem_area.problem_area_id=ticket.problem_area_id)
INNER JOIN status
ON status.status_id=ticket.status_id)
LEFT OUTER JOIN ticket_assign
ON ticket_assign.ticket_id=ticket.ticket_id)
WHERE ticket.status_id<>6

Not sure if this is the best way to write this code, but it works so I am not complaining. Once again thanks for pointing me to the right direction.

[neboat]

Turns out the code is ALMOST correct. At the end of the OUTER JOIN, am I able to do an INNER JOIN? I try to add the INNER JOIN at the end but it's not working....

SELECT *
FROM ((((((ticket
INNER JOIN customer
ON customer.customer_id=ticket.customer_id)
INNER JOIN company
ON company.company_id=customer.company_id)
INNER JOIN priority
ON priority.priority_id=ticket.priority_id)
INNER JOIN problem_area
ON problem_area.problem_area_id=ticket.problem_area_id)
INNER JOIN status
ON status.status_id=ticket.status_id)
LEFT OUTER JOIN ticket_assign
ON ticket_assign.ticket_id=ticket.ticket_id)

INNER JOIN employee
ON employee_employee_id=ticket_assign.employee_id

WHERE ticket.status_id<>6

[ZydoSEO]

I always put the OUTER JOINS last.

[syber]

It should work, make sure the employee file data actually matches the ticket data.