CODEWITHSUNDEEP
mysql
Drake M.
Drake M.

Reputation: 250

SELECT the same column with different AS

I've been searching for this for a couple of hours; not sure if I'm missing a keyword or if this is just an uncommon expectation, but here's the situation:

I'd like to select the same column from the same table multiple times in a single query. I want the end result to have exactly ONE row.

A basic example would be:

SELECT last AS firstLast, last AS secondLast FROM table WHERE last = 'name' OR last = 'other' LIMIT 1

The idea being: firstLast should contain the result when last is equal to name, secondLast should contain the result when last is equal to other.

The end result should look like this:

The current result I'd get would be:

If I didn't have the LIMIT in place, it would create a second row:

Thanks!

Upvotes: 1

Views: 247

Answers (2)

trincot
trincot

Reputation: 351369

You are looking for the CASE WHEN construct in combination with aggregation (MAX or MIN). BTW, you can use IN instead of OR :

SELECT MAX(CASE last WHEN 'name'  THEN last END) AS firstLast,
       MAX(CASE last WHEN 'other' THEN last END) AS secondLast
FROM   mytable
WHERE  last IN ('name', 'other')

No need to use LIMIT because with MAX you are aggregating the result.

Here is an SQL Fiddle

Note that the above query is not very interesting, as it just outputs what you put in the WHERE clause.

You could instead select something that goes with that value of last. For instance the age of the corresponding person:

SELECT MAX(CASE last WHEN 'johnson' THEN age END) AS johnsons_age,
       MAX(CASE last WHEN 'clark'   THEN age END) AS clarks_age
FROM   mytable
WHERE  last IN ('johnson', 'clark')

Upvotes: 1

Nir Levy
Nir Levy

Reputation: 12953

if you want the two columns to be different, you need to do a join between two instances of the same table. 

SELECT firstTable.last AS firstLast, secondTable.last AS secondLast from table
 as firstTable inner join table as secondTable
 WHERE firstTable.last = 'name' and secondTable.last = 'other' LIMIT 1

The only thing that's missing here is how to connect the joins (the on statement). you can do without if you just want to get one 'name other' result and no other columns, but if there are other columns on the table, like id, you should use it to do the join

Upvotes: 2

Related Questions