Using a condition in a SELECT statement

[Marcus]

Is there a way to write a select statement so that it does the
following:

Selects all students with a preferred addresses. If a student does
not have a preferred address, then select the first address that the
student has listed in the database.

So far this is what I have:

SELECT DISTINCT
PERSON.PERSON_ID ,
PERSON.STREET1 ,
PERSON.STREET2 ,
PERSON.CITY ,
PERSON.STATE ,
PERSON.COUNTRY ,
PERSON.ZIP_CODE ,
FROM
PERSON_ADDRESS
WHERE
PERSON_ADDRESS.PREFERRED_STATUS = '1';
The script above only gives me students with preferred addresses. I
am looking to add something like this:

<select statement goes here>
WHERE
PERSON_ADDRESS.PREFERRED_STATUS = '1'
If PERSON_ADDRESS.PREFERRED_STATUS = '0' Then
---SELECT TOP
-----PERSON.PERSON_ID ,
-----PERSON.STREET1 ,
-----PERSON.STREET2 .....
---FROM
-----PERSON_ADDRESS
End If
My example is just a prototype. Any ideas if something like this can
be accomplished?

Marcus
******

[Fred Zuckerman]

"Marcus" <to*******@yahoo.ca> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Is there a way to write a select statement so that it does the
following:

Selects all students with a preferred addresses. If a student does
not have a preferred address, then select the first address that the
student has listed in the database.

So far this is what I have:

SELECT DISTINCT
PERSON.PERSON_ID ,
PERSON.STREET1 ,
PERSON.STREET2 ,
PERSON.CITY ,
PERSON.STATE ,
PERSON.COUNTRY ,
PERSON.ZIP_CODE ,
FROM
PERSON_ADDRESS
WHERE
PERSON_ADDRESS.PREFERRED_STATUS = '1';
The script above only gives me students with preferred addresses. I
am looking to add something like this:

<select statement goes here>
WHERE
PERSON_ADDRESS.PREFERRED_STATUS = '1'
If PERSON_ADDRESS.PREFERRED_STATUS = '0' Then
---SELECT TOP
-----PERSON.PERSON_ID ,
-----PERSON.STREET1 ,
-----PERSON.STREET2 .....
---FROM
-----PERSON_ADDRESS
End If
My example is just a prototype. Any ideas if something like this can
be accomplished?

Marcus
******

1. Assuming that PERSON_ID is unique to each person, then I would first
create a query that selects all of the persons with a preferred address (the
query you've described above).

2. Then create a 2nd query that selects everyone who is NOT in the 1st query
(I think this is called an outer-join on ID with the 2nd ID criteria set to
Null).

3. Then create a 3rd query which UNIONs the 1st & 2nd query together.

Does that make sense?
Fred Zuckerman