How to SELECT from two diffrent columns in condition and join them from SQL

I am looking to join two columns as a single ... Here is an ide开发者_开发问答a ..

=======================================================
    id  | firstname  |        lastname
=======================================================
    1   | Peter      | Das
-------------------------------------------------------
    2   | Das        | James
-------------------------------------------------------
    3   | vector     | Call
______________________________________________________________
    4   | cans       | Das

The sql query should look like this (pseudocode)

SELECT (lastname WHERE firstname='Das' AND firstname WHERE lastname='Das' 
    ORDER BY ID DESC) AS name

And the resultset should look like this

    ======================
        name
    ======================
        cans
    ---------------------------------------
        James
    ---------------------------------------
        Peter

Now can anyone please tell me what is the exact query

Every idea hints suggestions are welcome

  SELECT CASE
             WHEN firstname = 'Das' THEN lastname
             ELSE firstname
         END AS name
    FROM table
   WHERE firstname = 'Das'
      OR lastname = 'Das'
ORDER BY id DESC

Looks like a job for a UNION query

SELECT id, firstName AS name
FROM table
WHERE lastName = 'Das'
UNION ALL
SELECT id, lastName AS name
FROM table
WHERE firstName = 'Das'
ORDER BY id DESC

You didn't tell us the name of your table. Assuming it's called mynames, this should work:

select case when firstname = 'Das' then lastname
else firstname end as name from mynames
where lastname = 'Das' or firstname = 'Das' order by id desc;

Here is some test code in MySQL I wrote to verify the solution works:

mysql> create table mynames(
    -> id int unsigned primary key auto_increment,
    -> firstname varchar(32) not null, 
    -> lastname varchar(32) not null
    -> ) engine=innodb;
Query OK, 0 rows affected (0.45 sec)

mysql> insert into mynames (firstname, lastname) values 
    -> ('Peter', 'Das'),
    -> ('Das', 'James'),
    -> ('vector', 'Call'),
    -> ('cans', 'Das');
Query OK, 4 rows affected (0.49 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from mynames;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | Peter     | Das      |
|  2 | Das       | James    |
|  3 | vector    | Call     |
|  4 | cans      | Das      |
+----+-----------+----------+
4 rows in set (0.00 sec)

mysql> select case when firstname = 'Das' then lastname
    -> else firstname end as name from mynames
    -> where lastname = 'Das' or firstname = 'Das' order by id desc;
+-------+
| name  |
+-------+
| cans  |
| James |
| Peter |
+-------+
3 rows in set (0.00 sec)