Select Multiple columns AS statement

[mrhankey]

Hi,

I have tried to Google this but cant find anything.

I want to use a select AS statement to select multiple columns.

i.e.

Select unitno country price AS propertyref

is this possible using multiple columns AS one reference?

many thanks

[codewall]

SELECT concat(unitno, ' ', country, ' ', price) AS propertyref FROM table_name

SNIP

[mrhankey]

thansk so much for your help.

i tried this:

SELECT CONCAT(properties.PropertyRef, ' ',  properties.SaleType, ' ', properties.UnitNo, ' ',  properties.PropertyType, ' ',properties.ProductName, ' ', properties.Development, ' ', properties.Country, ' ', properties.PriceofProperty) AS PropertyRef, PropertyID, ProductTypeID
FROM properties

however propertyref is showing as null?? have i made an error?

many thanks again

[codewall]

No, the result is null because mysql did not found any result set, I think you try this in small query or using only three fields, if your code is OK try to add another field.

[mrhankey]

thanks, i tried taking everything out other than this:

SELECT CONCAT(properties.PropertyRef) AS PropertyRef, PropertyID, ProductTypeID
FROM properties

and it shows as null yet if i take out the concat and as statements and just do a normal select the property ref etc are all there??

many thanks

[mrhankey]

sorry the above does work, but as soon as i add other columns i get null.

thanks

[mrhankey]

mmmm if i take propertyref out the concat it works. strange.

[smantscheff]

Any concatenation with NULL results in NULL.
Therefore you have to use the ifnull function:

SELECT CONCAT(ifnull(properties.PropertyRef,''), ' ', ifnull(properties.SaleType,'') ...

[mrhankey]

thansk for your help, when i execute the query i get the error "Incorrect parameter count in the call to native function 'ifnull'"

SELECT CONCAT(ifnull(properties.PropertyRef, ' '),  ifnull(properties.SaleType, ' '), ifnull(properties.UnitNo, ' '),  ifnull(properties.PropertyType, ' '), ifnull(properties.ProductName, ' '), ifnull(properties.Development, ' '), ifnull(properties.Country, ' '), ifnull(properties.PriceofProperty)) AS PropertyRef, PropertyID, ProductTypeID 
FROM properties

thanks again

[mrhankey]

with what you have said with the ifnull, will that return all the results even if the have null values in some of the columns in the AS statement?

Many thanks

[smantscheff]

Your last ifnull clause has only one parameter [ifnull(properties.PriceofProperty)].
The ifnull() function returns the first parameter except if it is NULL. In this case the second parameter gets returned.
If the concat() function gets a NULL as one of its parameters, it will return NULL.
Therefore each column has to be guarded against NULL values if you want to use it in a concat function.

[mrhankey]

hi thanks so much for your help, however i think i am being stupid as i am unsure what i need to add in the last ifnull?

SELECT CONCAT(ifnull(properties.PropertyRef, ' '),  ifnull(properties.SaleType, ' '), ifnull(properties.UnitNo, ' '),  ifnull(properties.PropertyType, ' '), ifnull(properties.ProductName, ' '), ifnull(properties.Development, ' '), ifnull(properties.Country, ' '), ifnull(properties.PriceofProperty))) AS PropertyRef, PropertyID, ProductTypeID FROM properties

is it [] i need to add around something?

many thanks again

[smantscheff]

You have not yet understood the ifnull function. Look into the manual.
Replace your blanks by empty strings (' ' => '').
Insert a blank (' ') after each ifnull() function call.
Experiment like this:

select ifnull('abc','xyz');
select ifnull('abc',NULL);
select ifnull(NULL,'abc');
select concat(NULL, 'abc' );
select concat(ifnull('abc','xyz'), ' ', 'xyz');
select concat(ifnull(NULL,'xyz'), ' ', 'xyz');

[mrhankey]

thanks for the reply, i am still unsure where i am missing and what i am missing from my query??

so do i only put the ifnull in front of the columns i want to return even if they are null?

thanks again

[smantscheff]

Yes, you have to apply ifnull to any column which you want to concat() and which might be NULL.
Try this:

SELECT CONCAT(ifnull(PropertyRef, ''), ' ', ifnull(SaleType, ''), ' ',ifnull(UnitNo, ''), ' ', ifnull(PropertyType, ''), ' ',ifnull(ProductName, ''), ' ',ifnull(Development, ''), ' ', ifnull(Country, ''), ' ',ifnull(PriceofProperty, ''))) AS PropertyRef, PropertyID, ProductTypeID FROM properties

[mrhankey]

thanks for that, that is where i thought you meant to add it at the end with a )

however when i execure this i get an error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS PropertyRef, PropertyID, ProductTypeID FROM properties LIMIT 0, 30' at line 1"

[smantscheff]

Do you have any reason to mistrust this error message? Delete the last closing bracket where the syntax error occurs.

[mrhankey]

hi, no reason to mistrust it. if i take the last closing bracket off i get the first error mentioned above.

many thanks for your help

[smantscheff]

SELECT CONCAT(ifnull(PropertyRef, ''), ' ', ifnull(SaleType, ''), ' ',ifnull(UnitNo, ''), ' ', ifnull(PropertyType, ''), ' ',ifnull(ProductName, ''), ' ',ifnull(Development, ''), ' ', ifnull(Country, ''), ' ',ifnull(PriceofProperty, '')) AS PropertyRef, PropertyID, ProductTypeID FROM properties

[mrhankey]

Thanks that worked, what was it that was wrong?

many thanks again