ober Posted March 21, 2011 Share Posted March 21, 2011 I have data stored like this: object1 | typeA object1 | typeB object2 | typeA etc. I want the output to be as follows: typeA | typeB object1 X | X object2 X Instead, I'm getting: typeA | typeB object1 X | object1 | X object2 X My query is as follows: SELECT object, MAX( IF(datatype='SCH','X','') ) AS 'SCH', MAX( IF(datatype='VV','X','') ) AS 'VV', MAX( IF(datatype='RS','X','') ) AS 'RS', MAX( IF(datatype='SP','X','') ) AS 'SP', MAX( IF(datatype='VL','X','') ) AS 'VL', MAX( IF(datatype='Blah','X','') ) AS 'Blah', MAX( IF(datatype='CT','X','') ) AS 'CT', MAX( IF(datatype='MltEdg','X','') ) AS 'MltEdg', MAX( IF(datatype='TW','X','') ) AS 'TW', MAX( IF(datatype='TG','X','') ) AS 'TG', MAX( IF(datatype='Argus','X','') ) AS 'Argus', MAX( IF(datatype='CS','X','') ) AS 'CS', MAX( IF(datatype='ND','X','') ) AS 'ND', MAX( IF(datatype='SIP','X','') ) AS 'SIP', MAX( IF(datatype='QVM','X','') ) AS 'QVM', MAX( IF(datatype='PR','X','') ) AS 'PR', MAX( IF(datatype='YF','X','') ) AS 'YF', MAX( IF(datatype='BC','X','') ) AS 'BC', MAX( IF(datatype='SC','X','') ) AS 'SC', MAX( IF(datatype='MS','X','') ) AS 'MS' FROM table WHERE datatype != 'sy' AND datatype != 'st' AND datatype != 'ry' GROUP BY object ORDER BY object The MAX/IF statement is for each 'type' mentioned above. Is there any way to do this without doing more looping and running more queries? Quote Link to comment https://forums.phpfreaks.com/topic/231268-query-issue/ Share on other sites More sharing options...
The Little Guy Posted March 21, 2011 Share Posted March 21, 2011 Have you tried something like this: SELECT object, MAX(DISTINCT IF(...)) ..... Quote Link to comment https://forums.phpfreaks.com/topic/231268-query-issue/#findComment-1190311 Share on other sites More sharing options...
ober Posted March 21, 2011 Author Share Posted March 21, 2011 That doesn't do anything. I've also tried Distinct on the object. No dice. Quote Link to comment https://forums.phpfreaks.com/topic/231268-query-issue/#findComment-1190315 Share on other sites More sharing options...
ober Posted March 21, 2011 Author Share Posted March 21, 2011 OK, it's not a problem with the query. If I select two rows from the same object and do nothing but save them, it updates the name of the object on one of them and the output magically works again. So now I'm wondering if the problem is an encoding problem? That column is current set to a collation of latin1_swedish_c1. Should I change it? The data is coming from 2 different sources so it is possible that the encoding on one of them could be different. Quote Link to comment https://forums.phpfreaks.com/topic/231268-query-issue/#findComment-1190326 Share on other sites More sharing options...
The Little Guy Posted March 21, 2011 Share Posted March 21, 2011 try using before your query: set names 'utf-8' and change the collation of both to "utf8_general_ci" I am not sure, but you may need to re-import the data. A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set. Suppose that we have an alphabet with four letters: 'A', 'B', 'a', 'b'. We give each letter a number: 'A' = 0, 'B' = 1, 'a' = 2, 'c' = 3. The letter 'A' is a symbol, the number 0 is the encoding for 'A', and the combination of all four letters and their encodings is a character set. Now, suppose that we want to compare two string values, 'A' and 'B'. The simplest way to do this is to look at the encodings: 0 for 'A' and 1 for 'B'. Because 0 is less than 1, we say 'A' is less than 'B'. Now, what we've just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): "compare the encodings." We call this simplest of all possible collations a binary collation. But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters 'a' and 'b' as equivalent to 'A' and 'B'; (2) then compare the encodings. We call this a case-insensitive collation. It's a little more complex than a binary collation. In real life, most character sets have many characters: not just 'A' and 'B' but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks. Also in real life, most collations have many rules: not just case insensitivity but also accent insensitivity (an "accent" is a mark attached to a character as in German 'ö') and multiple-character mappings (such as the rule that 'ö' = 'OE' in one of the two German collations). Quote Link to comment https://forums.phpfreaks.com/topic/231268-query-issue/#findComment-1190333 Share on other sites More sharing options...
ober Posted March 21, 2011 Author Share Posted March 21, 2011 So if I do this: SELECT object, HEX(object) FROM table The duplicates are being caused by an additional '0D' on the objects that are otherwise similar. Now I have to figure out how to strip those. I don't think it's necessarily an encoding thing anymore. Quote Link to comment https://forums.phpfreaks.com/topic/231268-query-issue/#findComment-1190348 Share on other sites More sharing options...
ober Posted March 21, 2011 Author Share Posted March 21, 2011 OK, so replacing all newline characters in the data in the database cleared the problem, so I just did that and now I'm replacing them as they're imported as well. Problem solved. Quote Link to comment https://forums.phpfreaks.com/topic/231268-query-issue/#findComment-1190371 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.