SQL query not working help needed

AdrianII

i need to compare the two fields from a database

ie if prediction = 1 and result = 1 then it will display the row in which this occurance happens

i tried the foolowing

"SELECT actual, predicted FROM Results WHERE actual=predicted;"


but get the following error ms - illegal mix of collations for operation '='

thanks for all the help guys

Creamy Goodness

you can't do WHERE actual = precdicted i don't think

you'd need to check that actual is equal to 1 and predicted is equal to 1.

because you more than likely have the actual column set to be an int of somesort but you're using a string in the statement.

AdrianII

sorry,
both colums are set to be strings, the results of the two columns can either be Home, draw or away, so i need it to display the results where

predicted = Home and result = Home

or

predicted = away and result = away


can this be done or is it way too hard

pickarooney

I would recommend storing these kind of values as integers for comparison purposes, e.g. home is 0 and away is 1. Whatever you're interpreting the SQL request with should output the string equivalent.

I don't really know why you get the error when comparing strings, but aht happens when you use:

SELECT actual, predicted FROM Results WHERE actual LIKE predicted

?

Also, your first post is a little confusing - all this command will return is a series of

Home, Home
Home, Home
Away, Away
Home, Home

etc.

I imagine you'd also want to return some other info from the rows where matches occur, no,

ravydavygravy

you nearly wrote it yourself... :-)

SELECT * FROM results 
 where 
   (actual = 'Home' AND predicted = 'Home') 
 OR 
   (actual ='Away' AND predicted = 'Away');

This should do the trick (MySQL anyway - not sure about T-SQL, but I suspect it'll be the same)

AdrianII

thanks for the posts, yep got the code turns out one column was a varchar and the other a string column

thanks for the replies