If/else construct returns integer instead of varchar

[gertie2008]

I'm trying to get the 3rd query to return a varchar, but it returns an integer.

Query one to prove that a varchar can be returned:

SELECT IF(1=1,0,'one'), IF(1=0,0,'one'); 
+-----------------+-----------------+ 
| IF(1=1,0,"one") | IF(1=0,0,"one") | 
+-----------------+-----------------+ 
| 0 | one | 
+-----------------+-----------------+ 
 

Query two to prove that I can return an integer(CoachedByContactID):

rs = st.executeQuery("SELECT Title, Author, OwnerID, ResponsiblePersonID, Synopsys, if(ResponsiblePersonID=0,(SELECT CoachedByContactID FROM details WHERE ContactID=6),OwnerID) as Myfield FROM book WHERE BookID=" + BookID);
 

Query three is my problem because I want to return a varchar(UserName instead of CoachedByContactID ) but it returns an integer:

rs = st.executeQuery("SELECT Title, Author, OwnerID, ResponsiblePersonID, Synopsys, if(ResponsiblePersonID=0,(SELECT UserName FROM details WHERE ContactID=6),OwnerID) as Myfield FROM book WHERE BookID=" + BookID);
 

[chaarmann]

The question is not clear.
If the last query returns an integer, then maybe UserName is an integer, or the expression ResponsiblePerson=0 evaluates to false, so that always OwnerID is returned. (the name OwnerID sounds like an integer). That's most likely why always an integer and no varchar is returned.

Please provide more details, especially some sample data where we can see the values of ResponsiblePersonID, OwnerId and UserName. Then we can answer the question without guessing around.

[gertie2008]

Thanks for the guidance. I will give 2 examples:

1.)Values: ResponsiblePersonID:6 OwnerID:50 Username='Clem'

rs = st.executeQuery("SELECT Title, Author, OwnerID, ResponsiblePersonID, Synopsys, if(ResponsiblePersonID='0',(SELECT Username FROM details WHERE ContactID=6),OwnerID) as Myfield FROM book WHERE BookID=" + BookID);
 

returns the following value: Myfield:50 (this works fine)

2.) Values: ResponsiblePersonID:6 OwnerID:50 Username='Clem'
(I change ResponsiblePersonID='0' to ResponsiblePersonID='6')

rs = st.executeQuery("SELECT Title, Author, OwnerID, ResponsiblePersonID, Synopsys, if(ResponsiblePersonID='6',(SELECT Username FROM details WHERE ContactID=6),OwnerID) as Myfield FROM book WHERE BookID=" + BookID);
 

returns the following value: Myfield:0 (this doesn't work at all, because the Username WHERE ContactID=6 is 'Clem' )
However, if I change 'SELECT Username' to something like 'SELECT Age' it will actually return the age value, because it is an integer, but as soon as I requist a field of type varchar with a string value like 'Clem', it returns 0.

[chaarmann]

Sorry for the late response,but I had no time to respond earlier because of a stressful work project.

Your query seems to be correct, syntactically and semantically.
Also you verified the fact in the first posting that mySqls if-statement can return a string and does not convert everything automatically to an integer. However if you still get 0 instead of the string, then it may come from the way you read the result set. Maybe you get a string back, but you read it later on in your program as an integer and not as a string? Because converting something like 'Clem' to an integer may result in 0. You can test that by executing

rs = st.executeQuery("SELECT Title, Author, OwnerID, ResponsiblePersonID, Synopsys, 'Clem' as Myfield FROM book WHERE BookID=" + BookID);
 

If you still get 0 back as Myfield here, then it's the case described above, and you should list your programming code how you copy the value from the resultset into a variable, so we can see what's wrong there, why it converts it to an integer afterwards.