Getting specific column name of a table

I am working on a project and I got stuck with a query in which I want to get the specific column name of a table whose value is “no.”

I have a table with name subscription and it has four fields; id(integer), email(varchar), sms(varchar), phone(varchar) and my email, sms and phone field have values either ‘yes’ or ‘no’.

Now I want to retrieve the name of only those columns which has a value ‘no’.
I want to execute this query in mysql.

I have tried the following query which is returning me the complete row wherever there is even one occurence of ‘no’:

SELECT * FROM test.subscription WHERE 'no' IN (email,sms,phone);

Like i have these two roes now i want to select the column name where value='no' and id=1

Like i have these two rows now i want to select the column name where value=’no’ and id=1

Comments 3

  • How about something cheap and cheerful using a case statement like:

    SELECT ID, 'ColName' = CASE
        WHEN email = 'no' THEN 'email'
        END, 
        'ColName2' = CASE
        WHEN sms = 'no' THEN 'sms'
        END, 
        'ColName3' = CASE
        WHEN phone = 'no' THEN 'phone'
        END
    FROM subscription
    where ID = 2
    
  • If i got the Question right, i think the following Query might work

    SELECT * 
    FROM test.subscription 
    WHERE 1=1
    and 
    (email = 'no'
    or
    email = 'no'
    or 
    sms = 'no'
    )
    and id=(your values)
    
  • I cant put this as a comment because of less privileges, but if you are looking to search a value which is held by a table (could be any column) – you might check this SO post

    Using the code from the same post –

    DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))
    
    DECLARE @SearchStr nvarchar(100)
    
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = 'no'
    SET @SearchStr2 = QUOTENAME('%' + 'no' + '%','''')
    
        SET @ColumnName = ''
        SET @TableName = '[test].[dbo].[subscription]'
    
        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND TABLE_NAME  = PARSENAME(@TableName, 1)
                    AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO @Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END 
    
    
    SELECT ColumnName, ColumnValue FROM @Results
    

发表评论

电子邮件地址不会被公开。 必填项已用*标注