|Subject:||Re: OT - SQL Help needed...
|Date:||Fri, 2 Dec 2016 16:28:51 -0500
|From:||"Larry DiGiovanni" <nospam@nospam>
FROM address_detail ADD
LEFT JOIN locality LOC ON ADD.locality_uid = LOC.locality_uid AND
LOC.locality_name = 'FREDS TOWN'
LEFT JOIN locality_alias ALS ON ALS.primary_locality_uid =
LOC.locality_uid AND ALS.locality_name = 'FREDS TOWN'
WHERE ADD.street_name LIKE 'MAIN%
AND (ALS.state IS NOT NULL OR LOC.state IS NOT NULL);
Moving the locality name check up into the join condition leaves NULL
columns if no matching locality or alias, but if there is a matching
The result set should contain the address, the locality, and the alias, if
either one exists. If neither exist, then the result set would be empty due
to the last condition.
If more than one 'FREDS TOWN' alias exists for the address locality, then
the above will have one row for each alias. I don't think it would make
sense that there'd be multiple exact matches on aliases, but if you were
allowing partial match, then there could be FREDBURG and FREDTOWN, all
matching FRED%. But this is, I think, a different question.