[thelist] Q on SQL 'ORDER BY' - part 3

[Ken Schaefer]

The easiest way to solve this is to show us what you expect the output to
look like. If you can't draw it, then it's probably impossible to do with
SQL. If you can come up with a results table, then it's (usually) doable.

What I think you want is a LEFT OUTER JOIN

SELECT
    a.st_id,
    a.st_name,
    b.con_id,
    b.con_name
FROM
    stations AS a
LEFT OUTER JOIN
    contacts AS b
ON
    a.st_id = b.st_id
ORDER BY
    a.st_name

I think you also want to move away from putting your join information in the
WHERE clause, as that's deprecated. Use explicit JOIN clauses instead. This
came up recently on the list IIRC.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "jsWalter" <jsWalter at torres.ws>
Subject: RE: [thelist] Q on SQL 'ORDER BY' - part 3


: I have 2 tables, COMPANYT and CONTACTs
:
: CONTACT has a foreign key to tie records to a particular record in
COMPANY.
:
: I'm trying to build a list of lists...
:
:     Sears - Sam
:     Sears - George
:     Sears - Sally
:
:     Wards - Henry
:     Wards - Sue
:
:     Kmart - Mike
:
:     Target
:
: The query I have...
:
:        SELECT stations.st_id, stations.st_name,
:               contacts.con_id, contacts.con_name
:
:          FROM stations, contacts
:
:         WHERE stations.st_id = contacts.st_id
:
:      ORDER BY stations.st_name;
:
:
: ...returns every CONTACT tied to every COMPANY. Not what I'm looking
for...
:
: If I remove the WHERE clause, the CONTACTs are tied to their proper
COMPANY,
: but the "orphaned" COMPANYs do not show up.
:
: Can any one enlighten me on this?