Carlos Angulo Martin Carlos Angulo Martin - 7 months ago 10
SQL Question

MySQL Select columns that matches with my data

Im trying to make a query in MySQL to retrieve certain data of one of my tables.

+---------------+-------------------+------------+
| friendA | friendB | firstDate |
+---------------+-------------------+------------+
| Peter | Susan | 05/05/2010 |
| Soshi | Peter | 05/05/2010 |
| Peter | Marie | 05/05/2010 |
+---------------+-------------------+------------+


Im trying to get all friends of Peter and firstDate field without fields that match with Peter's name.

Example of what I want.

+---------------+-------------------+
| friends | firstDate |
+---------------+-------------------+
| Susan | 05/05/2010 |
| Soshi | 05/05/2010 |
| Marie | 05/05/2010 |
+---------------+-------------------+


How could I get that?

Answer

Use a case expression to chose the non-Peter name:

select case when friendA <> 'Peter' then friendA else friendB end, firstDate
from tablename
where 'Peter' in (friendA, friendB)

Or do a UNION ALL:

select  friendA, firstDate
from tablename 
where friendB = 'Peter'
UNION ALL
select  friendB, firstDate
from tablename 
where friendA = 'Peter'
Comments