Search multiple columns and return one set of results 1

[jkmitchell56]

Hi there,

I have a table that has three fields that I want to be able to search (county, town, village).

The problem being that I then want to be able to return a single set of results in Alpha order.

For example, if I write three queries that each search one of the three fields for LIKE 'P%' to give me all records that start with the letter P, I then need to sort the combined results into a single list in ascending order.

I can write three queries but end up with the data in different fields (obviously) and can not find a way to merge the field values into a single column. Merging the 3 fields for each value will not give me what I want, I need to just see the value for the field that satisfies the select condition.

Thanks for any help offered.

John K Mitchell

http://www.forestsoftware.co.uk

 

[TonyGroves]

Would this do what you want:
[tt]
SELECT id,county result FROM tbl WHERE county LIKE 'P%'
UNION ALL
SELECT id,town FROM tbl WHERE town LIKE 'P%'
UNION ALL
SELECT id,village FROM tbl WHERE village LIKE 'P%'
[/tt]
This would return a dataset with two columns - 'id' and 'result'.

 

[jkmitchell56]

Thanks Tony,

Looks like an upgrade to mysql is called for - the version we run does not support the UNION command.



John K Mitchell

http://www.forestsoftware.co.uk