Select 2 columns, treat as one

[Wickersty]

Hi,

If I have a table in which are two colums job1 and job2. Both have the same possible values (ie job1 can be either cook, tv host, or sales and job2 can be ither cook, tv host, or sales).

I want to create a querie that says select DISTINCT jobs from job1 and job2... meaning, give me a list of all distinct jobs from both columns... keeping in mind that if "cook" is a distinct job from a job1 record, I dont want to pick up "cook" again from any job2 records.

Can someone help with this? I can't quite figure it out.

Thanks,

Jeff

 

[dalchri]

You need a union query:

SELECT job1
FROM MyTable
UNION
SELECT job2
FROM MyTable

The union will perform a distinct for you. If you need to preserve uniqueness, use UNION ALL

 

[Wickersty]

Thanks very much. But then how do I access the result?

ie:

SELECT job1
FROM MyTable
UNION
SELECT job2
FROM MyTable

in php i'd normally do $result['job1'] to get the result, but here i'd have a union of distincts across two columns... what would go in the $result['XXXX']?

Jeff

 

[dalchri]

I think the name is usually the name used in the first query or you could rename the columns to be consistent:

SELECT job1
FROM MyTable
UNION
SELECT job2
FROM MyTable

Would result in $result['job1']

-or-

SELECT job1 AS job
FROM MyTable
UNION
SELECT job2 AS job
FROM MyTable

Would result in $result['job']