DISTINCT list from two columns 1

[MMNZ]

I have a table named "test" with 4 columns:

+------+------+------+------+
| num | info | name1 | name2 |
+------+------+------+------+

And I want a DISTINCT list of the names from "name1" and "name2" (some of the same names may appear in either column, or may be blank).

I can easily get the DISTINCT listing of each column:

SELECT DISTINCT name1 FROM test;

and

SELECT DISTINCT name2 FROM test;

But I do not know how to JOIN them together into one DISTINCT result.

I am a MySQL novice, and am struggling with JOINing stuff together. Any help is greatly appreciated.

Thanks in advance.

 

[Tim1]

Hiya,

It is quite simple (once you know)....

DISTINCT works across the entire select statement when it is used, so all you need is

SELECT DISTINCT name1,
name2
FROM test;

However, you need to be aware that this will be all the distinct combinations, so if there are three Bobs in name 1, each having a different value in name 2, all three will be displayed as they are distinct.

This is the best that SQL can do without getting very complicated and starting to have to build temp tables.

HTH

Tim

 

[MMNZ]

Thanks for the reply Tim, but it dosen't quite get me to where I need to be.

Using:

SELECT DISTINCT name1,name2 FROM test;

As expected, I get two columns returned, one called "name1" showing all DISTINCT users, and one called "name2" showing all DISTINCT users.

I am looking for a result of ONE COLUMN with a DISTINCT lists of users created from both "name1" and "name2".

I am attempting a work around (since MySQL does not have a UNION function in version 3.23) and came up with this (that dosen't quite work - I get errors, but I think I am on the right track, thanks to an SQL book):

CREATE TEMPORARY TABLE temp_table TYPE=HEAP
SELECT DISTINCT name1 FROM test
INSERT INTO temp_table
SELECT DISTINCT name2 FROM test
INSERT INTO temp_table
SELECT DISTINCT * FROM temp_table;

Any thoughts?

Thanks again.

-Matt

 

[cDevX99]

Much easier way ....

select distinct concat( Name1, Name2) as Name from test ;

 

[cDevX99]

oh, ic, never mind ... disregard my last post.

 

[Tim1]

Hiya,

Sorry about the slight delay...

Yes, the idea of using a temp table would give you what you need...

Try:

SELECT DISTINCT name1 FROM test
INTO temp_table;

INSERT INTO temp_table
SELECT DISTINCT name2 FROM test;

SELECT DISTINCT * FROM temp_table;

Tim