SQL Statement to return certain records or plain text
I'm not sure how to explain it in the subject line correctly so i'll try to do so here. I have an SQL Query that runs through 3 tables, joins them up and returns a persons first and last name, their primary address, city state and university they attended (person table, addresses table and colleges table). now the way we have the db set up with foreign keys is that the record will default to a dummy record, most of the time that record will display "<Unassigned>" so that we don't have holes and null values in our foreign keys. but what im trying to do is run that query, to grab all the information and return it to my application, that or if the records are set to the dummy "<Unassigned>" records, just return "<Unassigned>". Currently it returns something formatted like this...
Smith, Bob (University of Florida) Jackson, Mississippi
but when it returns the records with the dummy values it shows this...
<Unassigned>, <Unassigned> (<Unknown>) <Unassigned>, <Unassigned>
Is there a way through the SQL that i could just vary that to say if the records return have the <Unassigned> value... then just display <Unassigned> once rather than a series of <Unassigned> text with commas and ( )? Here's what my query currently looks like...
SELECT concat_ws('', people.name_last, ', ', people.name_first, ' (', organizations.name_long, ') ', addresses_mailing.city, ', ', states.abbreviation), addresses_mailing.id_addresses_mailing FROM people LEFT JOIN addresses_mailing ON people.id_people = addresses_mailing.id_people LEFT JOIN organizations ON addresses_mailing.id_organizations = organizations.id_organizations LEFT JOIN states ON addresses_mailing.id_states = states.id_states ORDER BY people.name_last ASC;
not sure if this is even possible or if its some crazy subquery that i would need to make.
thanks in advance for any help,
-g