Being able to change what is displayed in the query or to display the results of a different column based on criteria in a WHERE clause is super helpful. Because this is so useful and something I use so frequently I writing a blog post on it.
Here is am example query I wrote. I want to display the Aurora "EndPoint" alias when I provide an IP Address. I've created two tables, one which has RDS/Aurora Cluster details, and one with RDS/Aurora Instance details. I've pulled this information from the AWS API and stored it locally to easily query it in a relational database. If the IP Address turns out to be the writer end point then I want the c.EndPoint column value to be displayed. If the IP Address is for reader end point then I want the c.ReaderEndpoint to be displayed. If there is only one instance in the cluster then this query will always return c.EndPoint (writer end point).
SELECT
IF(i.IsClusterWriter = 1, c.EndPoint, c.ReaderEndpoint ) AS alias
FROM RDSCluster c
INNER JOIN RDSInstance i ON (i.DBClusterIdentifier = c.DBClusterIdentifier)
WHERE i.IPAddress = 'xx.xx.xxx.xx';
With a case statement, I could also write it like this:
SELECT
(case when (i.IsClusterWriter = 1)
THEN
c.EndPoint
ELSE
c.ReaderEndpoint
END)
as alias
FROM RDSCluster c
INNER JOIN RDSInstance i ON (i.DBClusterIdentifier = c.DBClusterIdentifier)
WHERE i.IPAddress = 'xx.xx.xxx.xx'
Other examples:
https://stackoverflow.com/questions/8763310/how-do-write-if-else-statement-in-a-mysql-query
No comments:
Post a Comment