CODEWITHSUNDEEP
sql
user1853871
user1853871

Reputation: 249

Informix Outer Joins

I have a query which is below. This query should return multiple rows but only returns two as some rows dont have a joining row in the status table.Can anyone help me to fix this query so that it counts all rows even if there are no joining rows in the status table. 

SELECT count(h.h1_id)
FROM h1 h, owner o, ent e, status s
WHERE o.owner_id = h.owner_id AND e.enterprise_id = h.enterprise_id AND
h.herd_id=s.o_id AND s.o_type='H' AND h.code = 'QWE'
AND s.group_code!='123' AND s.status_code!='ABC'

Thanks!

Upvotes: 1

Views: 494

Answers (1)

Bill Gregg
Bill Gregg

Reputation: 7147

SELECT count(h.h1_id)
FROM h1 h
INNER JOIN owner o
ON o.owner_id = h.owner_id
INNER JOIN ent e
ON e.enterprise_id = h.enterprise_id
LEFT OUTER JOIN status s
ON h.herd_id=s.o_id 
where h.code = 'QWE'
AND ((s.o_type='H' AND s.group_code!='123' AND s.status_code!='ABC') OR (s.oid is null))

Upvotes: 1

Related Questions