Query issue

[ober]

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?

[The Little Guy]

Have you tried something like this:

 

SELECT object, MAX(DISTINCT IF(...)) .....

[ober]

That doesn't do anything.  I've also tried Distinct on the object.  No dice.

[ober]

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.

[The Little Guy]

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

[ober]

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.

[ober]

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.