Question: I have the following output:

| Username | shortname | data |
| --- | --- | --- |
| test1 | firm | Google |
| test1 | type | IT |
| test1 | agreed | 1 |
| test2 | firm | Facebook |
| test2 | type | IT |
| test2 | agreed | 1 |
| test2 | newsletter | 1 |

I want to make another column that makes the following: if username has the shortname "newsletter" and "newsletter" has the data "1" then "yes", if username does not have newsletter then "no"

So I want to have the following output:

| Username | yes/no |
| --- | --- |
| test1 | no |
| test2 | yes |

I made the following code:

```
SELECT distinct(if(shortname = 'newsletter' and data = 1, 'Yes', 'No')) as 'Yes/no', username
FROM prefix_user as u
JOIN prefix_user_info_data as uid ON uid.userid = u.id
JOIN prefix_user_info_field as uif ON uid.fieldid = uif.id

```

But this writes out yes or no in every single row

Answer: 
After doing some reaseach,I finally solved it.
It was a schema error.
**Id** column value is like **149633CM**
I had given data type of **Id** as INTEGER,but when I tried to load json with bq and schema as --autodetect, bq marked datatype of **Id as STRING.**

after that,I changed datatype of **Id column as STRING** in my schema in code.
And,it worked.The table is created and got loaded.

**But,I am not getting one thing,if starting 6 characters are numbers in Id column,why INTEGER is not wroking and STRING is working?**
because the data type is parsed on the whole field value and not only the first 6 characters. if you drop the last 2 characters, you can put INTEGER