CODEWITHSUNDEEP
sql-server
Bruce
Bruce

Reputation: 2213

sql question - outer join seems not to work

using: sql server

Here is what I am trying to accomplish. I have two tables one with 70 companies (company info, etc) and one table recording the incident numbers of those companies.

I like my sql result to return every company name whether a match is found or not. I thought an outer join will help, but it looks like my where clause is preventing this from happening:

I have tried full join, outerjoin, left join, same results .....

====> However if i remove the where clause then I get all companies names.

SELECT count(*) AS total_num, TS_NAME
FROM TTS_INCIDENTS RIGHT OUTER JOIN TS_COMPANIES
ON TS_COMPANIES.TS_ID=TTS_INCIDENTS.TS_COMPANYID
WHERE TS_ACTIVEINACTIVE = 0 AND (TS_INCIDENTTYPE=10 OR TS_INCIDENTTYPE=11)
GROUP BY TS_NAME
ORDER BY TS_NAME

Thanks Bruce

Upvotes: 1

Views: 148

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 220877

Even if I don't know which relation holds which columns in your example, I'm pretty sure you actually want to do this:

FROM TS_COMPANIES LEFT OUTER JOIN TTS_INCIDENTS
  ON TS_COMPANIES.TS_ID = TTS_INCIDENTS.TS_COMPANYID
 AND TS_ACTIVEINACTIVE = 0 
 AND (TS_INCIDENTTYPE = 10 OR TS_INCIDENTTYPE = 11)

i.e. select from companies and left outer join incidents to the companies, using the incident predicates as left outer join criteria, not as select criteria.

Note, in most RDBMS, a RIGHT OUTER JOIN usually has a heavy performance impact and should be avoided if possible (don't remember where I've read this. Might be an outdated fact).

Upvotes: 4

Phil Sandler
Phil Sandler

Reputation: 28016

If the WHERE clause is against the outer table in the JOIN, it effectively makes the JOIN an INNER JOIN (this is actually a very common mistake). It's hard to tell which table your WHERE clause criteria works against, since you don't qualify the tables.

However, my guess is you need to make the WHERE clauses a condition of the JOIN. Something like this:

SELECT 
    count(*) AS total_num, 
    TS_NAME 
FROM 
    TTS_INCIDENTS 
    LEFT OUTER JOIN TS_COMPANIES ON 
        TS_COMPANIES.TS_ID=TTS_INCIDENTS.TS_COMPANYID 
        AND TS_ACTIVEINACTIVE = 0 AND (TS_INCIDENTTYPE=10 OR TS_INCIDENTTYPE=11) 
GROUP BY 
    TS_NAME 
ORDER BY 
    TS_NAME

Upvotes: 0

Chains
Chains

Reputation: 13157

You need make to corrections:

  • change the RIGHT OUTER JOIN to a LEFT JOIN.
  • add an OR TTS_INCIDENTS.TS_COMPANYID is null to the WHERE clause

modifying the join is going to put the priority on all COMPANIES, instead of all INCIDENTS, as you have it now.
modifying the WHERE clause will allow Comapnies with NO INCIDENTS to also show-up in the list.

Upvotes: 0

Related Questions