Using CASE to affect AS

I’m struggling to come up with the correct syntax to use CASE in a query to change the name a column is fetched AS.

The database holds info for a “Roll of Honour”, so the main table: honour is a list of men who died in the wars with various columns, there is a column for service, regiment and battalion, these are foreign keys for IDs in other tables: services, regiments and battalions.
Selecting the name of the service is OK, I can use: SELECT services.name AS service
With the others I want to change the name according to the service.
So for example if service 1 is “Army” I’m happy to select regiments.name AS regiment
But if service 2 is “Navy” I don’t want to call it regiment, I want regiments.name AS ship because it’s the name of a ship, not a regiment.
Whenever I try I get syntax errors, but I’m not that familiar with using CASE.

This is how the query looks without any CASE conditions:-

SELECT honour.id, firstname, lastname, 
        services.name as service, regiments.name as regiment, battalions.name as battalion FROM Honour
	JOIN services ON honour.service = services.id
	LEFT JOIN regiments ON honour.regiment = regiments.id
	LEFT JOIN battalions ON honour.battalion = battalions.id
	WHERE war = :war ORDER BY lastname, firstname

The end result is I want to put the data into a PHP object, so I want the right column/property names.
I could post-process the data in PHP, but thought this would be better (if I knew how)

look at it this way – the result of a query is always a 2-dimensional table, consisting of rows and columns, and you can name the columns anything you want, but each column can have only one name

you might want to consider having separate columns for regiments and ships in your output, so that for a particular row if the service is Army then the regiment column has a value and the ship column is NULL, whereas if the service is Navy then the regiment column is NULL and the ship column has a value

another idea is to run separate queries for army and navy, in which case you can name the columns exactly for what they are

but personally i think you should do this in your application layer

1 Like

Got it. So it can’t be done (in this way).

I’ll probably go with that, I was just exploring the possibility of doing it by query.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.