Table a
Row1: id=1, text=row1

Table b
Row1: id=1, otherfield=2, text=row2
Row2: id=1, otherfield=1, text=row3

Here is the SQL:

SELECT a.text, b.text
FROM a
LEFT OUTER JOIN b on a.id = b.id
WHERE
a.id = 1
AND
(b.otherfield = 3 OR b.id IS NULL)

Right now nothing is returned if I specify a value for otherfield that
is not found in table b; I still want to get a.text even if there is
no matching row in b.

How do I still get a.text value from table a in this type of scenario?

In article <881ab36f-f2d5-400f-9e6b-63cc8f864b6a (AT) f36g2000pri (DOT) googlegroups.com>,
keytostars <starkeypictures (AT) gmail (DOT) com> wrote:
That depends on what you are actually trying to do. My first guess is
that you want to return a row with the data from "a" together with
any data from "b" that matches your otherfield condition, or else
empty "b" data.

What your query is doing is first generating these two rows:

a.id=1 a.test=row1 b.id=1 b.otherfield=2 b.text=row2
a.id=1 a.test=row1 b.id=1 b.otherfield=1 b.text=row3

and THEN applying the WHERE condition as a filter. Since there is no
row that either has b.otherfield=3 or b.id IS NULL, no rows are
returned to you.

Try this instead:

SELECT a.text, b.text
FROM a
LEFT OUTER JOIN b ON a.id = b.id AND b.otherfield = 3
WHERE a.id = 1

That will only include in the join the b rows you want, if any.

Cheers
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org