CODEWITHSUNDEEP
phpmysql
Ford
Ford

Reputation: 567

mysql 2 columns as 1 (not merged!)

Sounds simple but i cant work out if it is possible or not... As it sounds simple and is only a very small query it seems logical to do it in SQL rather than have to write php/etc to do this.

i have 2 columns that i want returning in 1 column but not merged and not if 1 of them in null, etc.

i have this (very basic)

SELECT DISTINCT PropertyDistrict as Area, PropertyTown as Area 
FROM tProperty

However this gives me 2 columns (both called area, with the results side by side), and ideally i want them as 1 column

COALESCE() and CONCAT() just merge them so this does not give me what i want. basically i would like the results as though they were all in the same column ( a row per result).

Any ideas if this is possible?

Update: the above results in :

# Area, Area
'Staffordshire', 'Stoke-on-Trent'
NULL, 'Blakesley'
NULL, 'Wick'

what i want is :

# Area
'Staffordshire'
'Stoke-on-Trent'
'Blakesley'
'Wick'

simply a list of all areas thanks!

Upvotes: 0

Views: 38

Answers (2)

Tim Hysniu
Tim Hysniu

Reputation: 1540

I think what you are looking for is:

SELECT DISTINCT PropertyDistrict as Area 
FROM tProperty 
WHERE PropertyDistrict IS NOT NULL
UNION
SELECT DISTINCT PropertyTown as Area 
FROM tProperty
WHERE PropertyTown IS NOT NULL

Upvotes: 1

Ford
Ford

Reputation: 567

Thanks All

Union works fine with:

SELECT DISTINCT PropertyTown Area
FROM tProperty 
WHERE PropertyTown != ''
   UNION
SELECT DISTINCT PropertyDistrict Area
FROM tProperty 
WHERE PropertyDistrict != ''
Order By Area ASC

And seem plenty fast enough thanks!

Upvotes: 2

Related Questions