jtelo Posted July 22, 2011 Share Posted July 22, 2011 Hi! I've a table 'books' with this data: yeardiscipline pré-escolargeometry 1math 1english I'm trying to do a mysql query to return the distinct disciplines with the year 'pré-escolar' and '1'. I've tried with SELECT distinct `discipline` FROM `books` WHERE `year`="Pré-escolar" OR `year`="1" But it only returns 'geometry'. Can someone help me? Thx. Quote Link to comment https://forums.phpfreaks.com/topic/242617-query-with-number-or-text/ Share on other sites More sharing options...
ebmigue Posted July 22, 2011 Share Posted July 22, 2011 Why not SELECT distinct `discipline` FROM `books` WHERE `year`='Pré-escolar' OR `year`='1' ? If that will not work, try: SELECT distinct `discipline` FROM `books` WHERE `year`='Pré-escolar' UNION SELECT distinct `discipline` FROM `books` WHERE `year`='1' But really, using the WHERE ...OR.. version should suffice. But the two queries are logically equivalent, so either of them should do. For current MySQL implementation, maybe the first version will fair well, performance wise. Quote Link to comment https://forums.phpfreaks.com/topic/242617-query-with-number-or-text/#findComment-1246069 Share on other sites More sharing options...
jtelo Posted July 22, 2011 Author Share Posted July 22, 2011 Thank you for the quick reply, but i'm getting the same result with both of your suggestions. I've more rows on that table with more years. If i make this query only with numbers, it returns the disciplines of both years. SELECT distinct `discipline` FROM `books` WHERE `year`='1' OR `year`='2' But with 'pré-escolar' and a number it doesn't work. Quote Link to comment https://forums.phpfreaks.com/topic/242617-query-with-number-or-text/#findComment-1246070 Share on other sites More sharing options...
jtelo Posted July 22, 2011 Author Share Posted July 22, 2011 MySQL version: 5.1.4.5 Quote Link to comment https://forums.phpfreaks.com/topic/242617-query-with-number-or-text/#findComment-1246179 Share on other sites More sharing options...
jtelo Posted July 22, 2011 Author Share Posted July 22, 2011 discovered the problem. The numbers had quotes in numbers. The correct syntax is the following: SELECT distinct `discipline` FROM `books` WHERE `year`=1 OR `year`="Pré-escolar" Quote Link to comment https://forums.phpfreaks.com/topic/242617-query-with-number-or-text/#findComment-1246213 Share on other sites More sharing options...
fenway Posted July 22, 2011 Share Posted July 22, 2011 I can't believe that's actually the problem -- there is little harm in quoting literals. Quote Link to comment https://forums.phpfreaks.com/topic/242617-query-with-number-or-text/#findComment-1246256 Share on other sites More sharing options...
jtelo Posted July 22, 2011 Author Share Posted July 22, 2011 I'm not an expert in this matter, but the query only returns the proper results this way. SELECT distinct `discipline` FROM `books` WHERE `year`="1" OR `year`="Pré-escolar" Only returns the discipline of of 'pré-escolar'. SELECT distinct `discipline` FROM `books` WHERE `year`=1 OR `year`=Pré-escolar Shows an SQL error: #1054 - Unknown column 'Pré' in 'where clause' SELECT distinct `discipline` FROM `books` WHERE `year`=1 OR `year`="Pré-escolar" Works! Quote Link to comment https://forums.phpfreaks.com/topic/242617-query-with-number-or-text/#findComment-1246280 Share on other sites More sharing options...
fenway Posted July 22, 2011 Share Posted July 22, 2011 In the query browser, or via php? Quote Link to comment https://forums.phpfreaks.com/topic/242617-query-with-number-or-text/#findComment-1246333 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.