Re: ranked subqueries vs distinct question

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.