Sub Query column problem

Hi

I was wondering if anybody could help me out here. This is for an App in ASP(classic) and MS SQL


A Quick explanation... I have 3 tables called ADMIN_SUB, ADMIN & STAFF.

What I want to do is depending on a particular row value in table ADMIN, either populate a field with either the forename + surname from ADMIN_SUB or the forename + surname from STAFF. (see EXAMPLE 1)


EXAMPLE 1
"SELECT ADMIN.admin_id, CASE WHEN ADMIN.staff_id IS NULL THEN ADMIN_SUB.forename ELSE STAFF.forename END AS forename, CASE WHEN ADMIN.staff_id IS NULL THEN ADMIN_SUB.surname ELSE STAFF.surname END AS surname FROM ADMIN LEFT OUTER JOIN STAFF ON ADMIN.staff_id = STAFF.staff_id LEFT OUTER JOIN ADMIN_SUB ON ADMIN.admin_id = ADMIN_SUB.admin_id"
(The above works fine and I can just access them as forename and surname, but when i use this as a sub query i can no longer get to their values.)

BUT

I need to try and use this as a SUB QUERY and the problem is I can't get at the values of forename and surname output-ed by
EXAMPLE 2. You can see how EXAMPLE 1 is placed inside EXAMPLE 2.

The columns I need from EXAMPLE 2 are ward_id, ward_name, phone, main_contact, notes, forename, surname
I have the following SQL (accessed as an ADO recordset)


EXAMPLE 2
rs.open "SELECT ward_id, ward_name, phone, main_contact, notes FROM WARD WHERE EXISTS (SELECT ADMIN.admin_id, CASE WHEN ADMIN.staff_id IS NULL THEN ADMIN_SUB.forename ELSE STAFF.forename END AS forename, CASE WHEN ADMIN.staff_id IS NULL THEN ADMIN_SUB.surname ELSE STAFF.surname END AS surname FROM (ADMIN LEFT OUTER JOIN STAFF ON ADMIN.staff_id = STAFF.staff_id LEFT OUTER JOIN ADMIN_SUB ON ADMIN.admin_id = ADMIN_SUB.admin_id) WHERE ADMIN.admin_id = main_contact) ORDER BY ward_name"





Can anybody help?
(Thanks in advance)

Cloudmonkey.

Can you just explain what it is you are trying to return from the sql statement?
You statements don't mean anything to us, because we don't know the database structure.
Just specify what fields from what tables you want to return, what the criteria is for returning them and any relationships between the tables.