CODEWITHSUNDEEP
sqloracle-databaseodbcleft-join
Emanuele Pavanello
Emanuele Pavanello

Reputation: 873

SQL - alternative to left outer join

There is a standard way in SQL to conut a number of rows joined to one table acepting also the 0? That is one example :

SELECT t1.id, COUNT(t2.*)
FROM t1 LEFT OUTER JOIN t2 ON ( t1.id = t2.id )
GROUP BY t1.id

I need a alternative because i use odbc with different databases, and on some databases the left join aren't supported.

Upvotes: 1

Views: 5520

Answers (2)

ThinkJet
ThinkJet

Reputation: 6745

SELECT 
  t1.id, 
  (SELECT COUNT(*) FROM t2 WHERE t2.id = t1.id) as t2_count 
FROM t1

Upvotes: 6

Vlisisids Akis
Vlisisids Akis

Reputation: 81

Two Options: Option 1: use the (+) operator:

SELECT t1.id, COUNT(t2.*)
  FROM t1, t2
 WHERE t2.id(+) = t1.id
 GROUP BY t1.id

I don;t know if it works on all drivers. Option 2 that will work with all drivers is to create a view and create the view instead.

Upvotes: 0

Related Questions