[thelist] SQL - HAVING and OUTER LEFT JOIN

[Scott Dexter]

> With regards to SQL, can someone explain to me in
> simple terms what the 
> HAVING clause does, and what an OUTER LEFT JOIN
> does?
> 

I'll try (is Rudy still around?) and please bear in
mind this is how I understand things to work; they may
not be 100.0000% accurate (standard disclaimer)...

HAVING is used when you're using aggregate clauses,
e.g. counting rows of something. The simplest example
I can think of is one I use a lot:

Say I have a table with rows (of people, let's say),
and each row (person) has a group id. Well, I want a
list of group ids that have more than 50 members
(rows) --

Select groupid from tbTable group by groupid HAVING
count(groupid)>50

The HAVING part of the clause there restricts the
output to those groups who have more than 50 members.


LEFT OUTER JOIN is one way to specify a JOIN behavior.
I might reccommend some reading on joins in general
(the MS SQL Server Books Online are a pretty good
resource), but to gloss over the LEFT OUTER JOIN:

You're joining two tables, tbParts and tbOrders, on
the part id. An inner join would pull up results where
the part id is in both tables and only in both tables.
 An outer join will pull up records where the part id
is on one table, but not the other. How you specify
which table is which with the "RIGHT" or "LEFT" part
of the join statement:

Select * from tbParts p LEFT OUTER JOIN tbOrders o on
p.partid=o.partid

The LEFT table --tbParts-- is the open-ended table.
You'll get all the rows from tbParts and also matching
rows from tbOrders.

Note:

Select * from tbtbParts p LEFT OUTER JOIN tbOrders o
on p.partid=o.partid

IS THE SAME AS

Select * tbOrders o RIGHT OUTER JOIN tbParts p on
p.partid=o.partid


Make sense?

sgd