CODEWITHSUNDEEP
mysqlsql
higeat
higeat

Reputation: 55

Query to retrieve column names

Table Structure:

enter image description here

Scenario:

To retreive name of columns if they are not null so for Patch_No = 0.02 would be Champion = Katarina

This is the query I came up with which unfortunately doesn't work EDIT The query below gives me Passive, Q, W, E, R so it seems like there is something wrong with the and (passive is not null or [...])

Query:

 SELECT column_name 
from information_schema.columns, patches.champions
 where 
table_name = 'champions'
AND (column_name ='Passive' OR column_name='Q' OR column_name ='W' OR column_name='E' or            column_name='R')
AND (Passive is not null or Q is not null or W is not null or E is not null or R is not null)
AND Patch_No = '0.02'
AND Champion = 'Katarina'

I tried to just test for Passive just for this patch and champion and it always gives me 5 passives like it doesn't take Patch_No and Champion restrictions into consideration

EDIT2 

select isc.COLUMN_NAME, c.* 
from INFORMATION_SCHEMA.COLUMNS isc, patches.champions c
where TABLE_NAME = 'champions' 
AND Patch_No = 0.02
AND Champion = 'Katarina'

and (
    (isc.COLUMN_NAME = 'Passive' and c.Passive is not null)
    or (isc.COLUMN_NAME = 'Q' and c.Q is not null)
    or (isc.COLUMN_NAME = 'W' and c.W is not null)
    or (isc.COLUMN_NAME = 'E' and c.E is not null)
    or (isc.COLUMN_NAME = 'R' and c.R is not null)
)
order by
     c.ID

Upvotes: 0

Views: 67

Answers (2)

higeat
higeat

Reputation: 55

So this is what actually worked out for me I mostly took code from G B thanks for help!

SELECT column_name 
from information_schema.columns, patches.champions
where 
table_name = 'champions'
AND ((column_name ='Passive' and Passive!='')
or (column_name='Q' and Q!='')
or (column_name='W' and W!='')
or (column_name='E' and E!='')
or (column_name='R' and R!='')

)

 AND Patch_No = '0.02'
AND Champion = 'Katarina'

Upvotes: 0

G B
G B

Reputation: 1462

Try this.

NOTE that in the where clause I have removed the quotes from comparison for patch_no on the assumption that patch_no was a numeric datatype. if it is in fact a string you will need to put them back in.

select isc.COLUMN_NAME, c.* 
from INFORMATION_SCHEMA.COLUMNS isc, patches.champions c
where TABLE_NAME = 'SO_27769554' 
AND Patch_No = .02
AND Champion = 'Katrina'

and (
        (isc.COLUMN_NAME = 'pasive' and c.pasive is not null)
        or (isc.COLUMN_NAME = 'q' and c.q is not null)
        or (isc.COLUMN_NAME = 'w' and c.w is not null)
        or (isc.COLUMN_NAME = 'e' and c.e is not null)
        or (isc.COLUMN_NAME = 'r' and c.r is not null)
    )
order by
    c.id

Upvotes: 1

Related Questions