On May 14, 2008, at 9:55 AM, Karsten Hilbert wrote:
On Wed, May 14, 2008 at 09:35:10AM -0500, Decibel! wrote:
SELECT name, zip, zip='04317' AS zipmatch
FROM urb LEFT JOIN streets ON (streets.urb_id = urb.urb_id )
ORDER BY zipmatch DESC, name
;
The view dem.v_zip2data (which I erronously left out in my
first post) does just that - it joins streets to urbs
thereby providing urbs with zip codes from the streets
table. It, however, only joins those rows which do have a
zip code. That leaves out those cities which don't. Which
makes me want to UNION on the dem.urb table in the initial
problem.
Doesn't the "LEFT JOIN" in decibel's suggestion account for that?
i.e. -- it isn't limited to just rows which have a zip code.
Rows in the result set with no corresponding row in the streets table
will just be represented with a NULL zip code.
If the view is limited as you describe, don't use is. Do the LEFT
JOIN explicitly for yourself and the query should do exactly what you
wish.
Just for a lark, run that code. I'll bet it works for you.
