0

Let's assume we have table logins:

id| name  | lastname
---------------------
1 | mark  | johnson
2 | jack  | sparrow
3 | bruno | mark

We all know, that we can query SQL similiar to: SELECT name FROM login WHERE id = 1; and get in return mark.

I am looking for query like:

SELECT column_name FROM logins WHERE value = 'mark';

which will result in name, lastname or even

SELECT column_name FROM logins WHERE value = 'mark' AND id = 1;

which will result in name.

EDIT: The question is more complex than simple answers given. Real problem is how to select column name from 100+ columns, when we know expected value. For example we know that somewhere in table there is value 17,58, but we want to find column name.

MacEncrypted
  • 184
  • 9
  • 2
    I didn't understand your problem. Can you provide some more explanation of your problem ? – Anik Islam Abhi Oct 28 '14 at 08:26
  • MySQL / PostgreSQL ? – Raptor Oct 28 '14 at 08:27
  • Please explain your question. I cant understand it clearly. – Arun M R Nair Oct 28 '14 at 08:31
  • Read [this](http://stackoverflow.com/questions/12550368/search-all-columns-of-a-table-using-a-single-where-condition-with-single-keyword) – Raptor Oct 28 '14 at 08:31
  • http://stackoverflow.com/questions/5350088/how-to-search-a-specific-value-in-all-tables-postgresql –  Oct 28 '14 at 08:41
  • 1
    @a_horse_with_no_name actually http://stackoverflow.com/questions/5350088/how-to-search-a-specific-value-in-all-tables-postgresql is similiar to what I am looking for. I will write some sh script which will do that for me. – MacEncrypted Oct 28 '14 at 08:52
  • 2
    100+ columns??? Sounds to me like it's worth considering redesigning your database. E.g. if you can replace all your columns with one table with name-value pairs your "tricky" problem becomes "select keyname from keytable where value = 'value'"... – mlinth Oct 28 '14 at 08:59
  • 100+ columns? That's scary.... Check your datamodel first, before you're in real deep trouble – Frank Heikens Oct 28 '14 at 09:32
  • 1
    The answer is very, very simple: Normalisation – Strawberry Oct 28 '14 at 09:39

4 Answers4

1

Getting the column name directly is not possible however using the case statement you can construct the query to return the column name for a matched value something as

select
id,
case 
 when name = 'mark' then 'name' 
 when lastname = 'mark' then 'lastname'
 else 'Nothing'
end as column_name 
from logins

DEMO

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • Well in this case we can just remove the 'Nothing' block and by adding the where at the end as `where name = 'mark' or lastname='mark'` – Abhik Chakraborty Oct 28 '14 at 08:36
0

In PostgreSQL you can use the hstore extension for this (I don't think there is anything remotely similar available in MySQL):

with login_data as (
  select id, 
         skeys(hstore(l)) as column_name,
         svals(hstore(l)) as column_value
  from logins as l
  where avals(hstore(l)) @> array['mark']
) 
select id, column_name
from login_data
where column_value = 'mark'
;

SQLFiddle example: http://sqlfiddle.com/#!15/115d5/1

But the performance will most probably be quite horrible.

-1

if you want to get mark if name in (name) Or in (lastname) id think this will help You

SELECT column_name FROM logins WHERE ( name  = 'mark' Or lastname = 'mark') AND id = 1;
Kerollos Adel
  • 9
  • 2
  • this does not make sense. If `id = 1` is in `WHERE` clause, you don't need to search by names. – Raptor Oct 28 '14 at 08:31
-1

Something like this if you don't know id of the user:

SELECT DISTINCT name FROM logins WHERE name = 'mark';
Wolf87
  • 540
  • 3
  • 11
  • 29