Help with query

[auntier]

I'm looking for the best approach to write a query for the following scenario:
I have an account table and a contact table. The contact table has various fields such as name, phone numbers, unique id, and a primary designation that is not a required field. These records are then associated back to the account records.

I'm attempting to write a query that selects at least one contact from each account record. I can easily write the query to select the contact where the primary designation is set to yes but I'm having difficulties figuring out how to select one contact record per account record when no primary designation exists. Is it possible to write a query to select a record where the primary designation is set to yes and then if that does not exist maybe select the record with the lowest or highest id number per account?

[chazzy]

sure why not?

(btw,might help if you posted the actual sql you're using currently)

[auntier]

Thanks a bunch.
Here is my uncomplete query:
select name, email, address, id
from account a, contact b
where primaryid = 1
and a.accountid=b.accountid
(next statement is what I'm missing to determine if primaryid != 1 then select one contact from each account)

[chazzy]

You can use a left join, and then just getting 1 row (either via a max() aggregate or LIMIT/TOP statement). The left join will allow the query to function even if no row is present.

[drallab]

If the primaryid is either 1 or null, you could try using isnull in the select statement like so:

select name, email, address, isnull(id, XXX) as id
from account a, contact b
where primaryid = 1
and a.accountid=b.accountid
XXX can be another column, query, etc.

The isnull checks the id column first for a null value, if there is a value (0, 1, etc) then it will use that value. If id is null, then it will use the whatever XXX is. :)