Jump to content

Query issue


Recommended Posts

I have data stored like this:


object1 | typeA

object1 | typeB

object2 | typeA




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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.