Grouping fields for selection 1

[y2k1981]

I'm pretty sure this is possible, don't know if maybe I need to use an index, nor do I know whether I should use an index.

What I have is contacts table with nearly 30 columns. 10 are for personal details, and 10 are for office details. I then have two fields, one to say add personal details to public address book, another to say add office details to public address book. There is no "public address book" table, I'm just going to pull the details from the contacts table where these values are set to Y.

What I want to do is basically the following, using one select statement:

SELECT home_address1, home_address2 ... FROM contacts WHERE home_public = 'Y', SELECT office_address1 ... FROM contacts WHERE office_public = 'Y'

Do I need to create an "alias" of some kind for the 10 "home" fields and another for the 10 "office" fields, or how do I do this?

any help would be greatly appreciated

 

[TonyGroves]

If you want separate records for person details and office details, you could try:
[tt]
SELECT
'H' loc, home_address1 address1, home_address2 address2 ...
FROM contacts
WHERE home_public='Y'
UNION ALL
SELECT
'O', office_address1, office_address2 ...
FROM contacts
WHERE office_public='Y'
[/tt]
This will return up to two records per contact, with the fields named "loc" (value H or O), "address1", "address2", etc.

If this is not what's required, maybe you could elaborate a bit.

 

[y2k1981]

yes, I think that's what I'm after. Only problem is that office has 2 extra fields (company and job title) which home doesn't, and because a union query requires the same amount of fields, I wouldn't be able to selec them.

However, having said that, I could just select those 2 fields later on in the page if required.

Thanks for your help

 

[TonyGroves]

You could use nulls where those two extra fields don't apply:
[tt]
SELECT
'H' loc,
home_address1 address1,
home_address2 address2,
...
NULL company,
NULL jobtitle
FROM contacts
WHERE home_public='Y'
UNION ALL
SELECT
'O',
office_address1,
office_address2,
...
company,
jobtitle
FROM contacts
WHERE office_public='Y'
[/tt]