Instead of `COALESCE(a.addressid,0) AS addressexists`, use `CASE`:

    CASE WHEN a.addressid IS NOT NULL 
           THEN 1
           ELSE 0
    END AS addressexists

or the simpler:

    (a.addressid IS NOT NULL) AS addressexists

This works because `TRUE` is displayed as `1` in MySQL and `FALSE` as `0`.

    SELECT c.name, IF(a.addressid IS NULL,0,1) AS addressexists
    FROM customers c
    LEFT JOIN addresses a ON c.customerid = a.customerid
    WHERE customerid = 123

Careful if you&#39;re coming from C/C++ and expecting this to work:

    select if(name, 1, 0) ..

Even if &#39;name&#39; is not NULL, unlike in C, a false-condition still triggers and the above statement returns 0. Thus, you have to remember to explicitly check for NULL or empty string:

     select if(name is null or name = &#39;&#39;, 0, 1)


*PS [Eugen&#39;s example up above is correct][1], but I wanted to clarify this nuance as it caught me by surprise.*


  [1]: https://stackoverflow.com/a/9387875/2949533


    SELECT 
        c.name, 
        CASE WHEN a.addressid IS NULL THEN 0 ELSE 1 END AS addressexists
    FROM customers c
    LEFT JOIN addresses a ON c.customerid = a.customerid
    WHERE customerid = 123



Another method without WHERE, try this..

Will select both Empty and NULL values
    
    SELECT ISNULL(NULLIF(fieldname,&#39;&#39;))  FROM tablename




It will set null if it is an empty string, then be true on that also.

You can actually use an IF statement in the latest versions of MySQL.

```
IF(expr,if_true_expr,if_false_expr)
```

IE:

```
SELECT name, IF(ISNULL(name), &#39;robot&#39;, &#39;human&#39;) AS type
FROM visitors
```

If within TSQL, you can try :

    SELECT IIF(a.addressid IS NULL, 0, 1) AS addressexists

SQL Server should work


I have a 3rd party C++ DLL that I call from C#.

The methods are static.

I want to abstract it out to do some unit testing so I created an interface with the static methods in it but now my program errors with:


&gt;  The modifier &#39;static&#39; is not valid for this item
    
    MyMethod cannot be accessed with an instance reference; qualify it with a type name instead

How can I achieve this abstraction?

My code looks like this

    private IInterfaceWithStaticMethods MyInterface;
    
    public MyClass(IInterfaceWithStaticMethods myInterface)
    {
      this.MyInterface = myInterface;
    }
    
    public void MyMethod()
    {
      MyInterface.StaticMethod();
    }

How can I implement static methods on an interface?

I&#39;m thinking about embedding arbitrary JSON in the DOM like this:

    &lt;script type=&quot;application/json&quot; id=&quot;stuff&quot;&gt;
        {
            &quot;unicorns&quot;: &quot;awesome&quot;,
            &quot;abc&quot;: [1, 2, 3]
        }
    &lt;/script&gt;

This is similar to the way one might store an arbitrary HTML template in the DOM for later use with a JavaScript template engine. In this case, we could later retrieve the JSON and parse it with:

    var stuff = JSON.parse(document.getElementById(&#39;stuff&#39;).innerHTML);

[This works][1], but is it the best way? Does this violate any best practice or standard?

&lt;i&gt;Note: I&#39;m not looking for alternatives to storing JSON in the DOM, I&#39;ve already decided that&#39;s the best solution for the particular problem I&#39;m having. I&#39;m just looking for the best way to do it.&lt;/i&gt;


  [1]: http://jsbin.com/epodeh/edit#javascript,html

Best practice for embedding arbitrary JSON in the DOM?

I&#39;m working with a little display complication here.  I&#39;m sure there&#39;s an IF/ELSE capability I&#39;m just overlooking.

I have 2 tables I&#39;m querying (customers, addresses).  The first has the main record, but the second may or may not have a record to LEFT JOIN to.

I want to display a zero if there is no record in the addresses table. 
And I want to only display 1, if a record exists.

What I&#39;ve attempted so far:


    SELECT c.name, COALESCE(a.addressid,0) AS addressexists
    FROM customers c
    LEFT JOIN addresses a ON c.customerid = a.customerid
    WHERE customerid = 123

This first example does not do it.  But I may be utilizing COALESCE wrong.

How can I display a 0, if null, and a 1, if something exists?





MySQL IF NOT NULL, then display 1, else display 0

I'm working with a little display complication here. I'm sure there's an IF/ELSE capability I'm just overlooking.
I have 2 tables I'm querying (customers, addresses). The first has the main record, but the second may or may not have a record to LEFT JOIN to.
I want to display a zero if there is no record in the addresses table.
And I want to only display 1, if a record exists.
What I've attempted so far:
<pre><code class="hljs language-sql">SELECT c.name, COALESCE(a.addressid,0) AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123
</code></pre>
This first example does not do it. But I may be utilizing COALESCE wrong.
How can I display a 0, if null, and a 1, if something exists?

I have one `UNIQUE` field in my table, and I need to search over it quickly. Do I need to index it?

Do searches over unique fields and indexed fields vary in speed or resource usage?

MySQL: unique field needs to be an index?

Only today I realized that I was missing this in my PHP scripts:

    mysql_set_charset(&#39;utf8&#39;);

All my tables are InnoDB, collation &quot;utf8_unicode_ci&quot;, and all my VARCHAR columns are &quot;utf8_unicode_ci&quot; as well. I have `mb_internal_encoding(&#39;UTF-8&#39;);` on my PHP scripts, and all my PHP files are encoded as UTF-8.

So, until now, every time I &quot;INSERT&quot; something with diacritics, example:

    mysql_query(&#39;INSERT INTO `table` SET `name`=&quot;J&#225;u&#242; I&#241;e&quot;&#39;);

The &#39;name&#39; contents would be, in this case: `J&#195;&#161;u&#195;&#178; I&#195;&#177;e`.  

Since I fixed the charset between PHP and MySQL, new INSERTs are now storing correctly. However, I want to fix all the older rows that are &quot;messed&quot; at the moment. I tried many things already, but it always breaks the strings on the first &quot;illegal&quot; character. Here is my current code:

    $m = mysql_real_escape_string(&#39;&#191;&lt;?php echo &quot;&#172;&lt;b&gt;\&#39;PHP &amp;aacute; (&#225;)ţări&#238;ş &lt;/b&gt;&quot;; ?&gt; ă-ţi abcdd;//;&#241;&#231;&#180;พดแทฝใจค&#231;ă&#226;ξβψδπλξξςαยน&#241; ;&#39;);
	mysql_set_charset(&#39;utf8&#39;);
	mysql_query(&#39;INSERT INTO `table` SET `name`=&quot;&#39;.$m.&#39;&quot;&#39;);
	mysql_set_charset(&#39;latin1&#39;);
	mysql_query(&#39;INSERT INTO `table` SET `name`=&quot;&#39;.$m.&#39;&quot;&#39;);
	mysql_set_charset(&#39;utf8&#39;);
	
	$result = mysql_iquery(&#39;SELECT * FROM `table`&#39;);
	while ($row = mysql_fetch_assoc($result)) {
		$message = $row[&#39;name&#39;];
		$message = mb_convert_encoding($message, &#39;ISO-8859-15&#39;, &#39;UTF-8&#39;);
		//$message = iconv(&quot;UTF-8&quot;, &quot;ISO-8859-1//IGNORE&quot;, $message);
		mysql_iquery(&#39;UPDATE `table` SET `name`=&quot;&#39;.mysql_real_escape_string($message).&#39;&quot; WHERE `a1`=&quot;&#39;.$row[&#39;a1&#39;].&#39;&quot;&#39;);
	}

It &quot;UPDATE&quot;s with the expected characters, except that the string gets truncated after the character &quot;ă&quot;. I mean, that character and following chars are not included on the string.

Also, testing with the &quot;iconv()&quot; (that is commented on the code) does the same, even with //IGNORE and //TRANSLIT

I also tested several charsets, between ISO-8859-1 and ISO-8859-15.

Convert latin1 characters on a UTF8 table into UTF8

MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?

  

      INSERT INTO Results
        (
         People,
         names,
        )
        VALUES
        (
         (
           SELECT d.id
           FROM Names f
           JOIN People d ON d.id  = f.id
         ),
        
         (
          &quot;Henry&quot;
         ),
        );

I **WANT** to populate the new table with all results returning from this subquery. How do I do this without getting a **ERROR 1242 (21000): Subquery returns more than 1 row**


MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?

I am using MySQL workbench - What is the difference between duration and fetch times when a query is run?

Also is there a way I can enable the microsecond option in MySQL?

mysql duration and fetch time

I have a MySQL dump of one of my databases. In it, there are DEFINER clauses which look like, 

    &quot;DEFINER=`root`@`localhost`&quot; 

Namely, these DEFINER clauses are on my CREATE VIEW and CREATE PROCEDURE statements. Is there a way to remove these DEFINER clauses from my dump file? 

Remove DEFINER clause from MySQL Dumps

This is pretty basic but I was coding and started wondering if there was a pythonic way to check if something does not exist.  Here&#39;s how I do it if its true:

    var = 1
    if var:
        print &#39;it exists&#39;

but when I check if something does not exist, I often do something like this:

    var = 2
    if var:
        print &#39;it exists&#39;
    else:
        print &#39;nope it does not&#39;

Seems like a waste if all I care about is knIs there a way to check if something does not exist without the else?

Pythonic way to check if something exists?

I was trying to write a Bash script that uses an `if` statement.
 
    if[$CHOICE -eq 1];

The script was giving me errors until I gave a space before and after `[` and before `]` as shown below:

    if [ $CHOICE -eq 1 ];

My question here is, why is the space around the square brackets so important in Bash?

Why should there be spaces around &#39;[&#39; and &#39;]&#39; in Bash?

I think I ran into a bug in Window&#39;s batch scripting.

I cannot set variables that are in an expanded if statement.

Here is an isolated part of my script:

    @echo off

    set success=1
    set Version=12345
    set Target=Client
    set Type=456
    set dir=
    set zip=
    
    if &quot;%Version%&quot;==&quot;&quot; set success=0
    if &quot;%Type%&quot;==&quot;&quot; set success=0
    
    if 1==1 set test=42
    
    if %success%==1 (
    	set test2=57005
    	if &quot;%Target%&quot;==&quot;Client&quot; (
    		set dir=ModName v%Version%
    		set zip=ModName v%Version% %Type%.zip
    		echo Version: %Version%
    		echo    Type: %Type%
    		echo.
    		echo  Target: %Target%
    		echo     dir: %dir%
    		echo     zip: %zip%
    		echo.
    		echo    test: %test%
    		echo   test2: %test2%
    	)
    ) else (
    	echo Not successful.
    )

This is the output from a brand new instance of cmd:

    C:\Users\RandomClown\Desktop&gt;test.bat
    Version: 12345
       Type: 456
    
     Target: Client
        dir:
        zip:
    
       test: 42
      test2:

What you should notice is that the single line if statement correctly sets stuff.
The multiline if will perform anything that is NOT a set. I dont think I missed anything.
Also, the multiline if statement is correctly executing the right lines, as the `else ( echo Not successful. )` lines did not execute.

Why did the lines not execute?

Windows Batch Variables Won&#39;t Set

I have the following if statement:

    String newStr4 = strr.split(&quot;2012&quot;)[0];
    if (newStr4.startsWith(&quot;Mon&quot;)) {
        str4.add(newStr4);
    }

I want it to include `startsWith`  `Mon` `Tues` `Weds` `Thurs` `Friday` etc. Is there a simple way to this when using strings? I tried `||` but it didn&#39;t work.


How to check if a string starts with one of several prefixes?

I have two tables, one is for news and the other one is for comments and I want to get the count of the comments whose status has been set as approved.

    SELECT
        ccc_news . *, 
        count(if(ccc_news_comments.id = &#39;approved&#39;, ccc_news_comments.id, 0)) AS comments
    FROM
        ccc_news
        LEFT JOIN
            ccc_news_comments
        ON ccc_news_comments.news_id = ccc_news.news_id
    WHERE
        `ccc_news`.`category` = &#39;news_layer2&#39;
        AND `ccc_news`.`status` = &#39;Active&#39;
    GROUP BY
        ccc_news.news_id
    ORDER BY
        ccc_news.set_order ASC
    LIMIT 20 

But the problem with this query is that the minimum value that is fetched for the comments column is 1 whether there is any comment existent corresponding to that news or not.

Any help would be highly appreciable.

Content Type	Original Author	Original Content on Stackoverflow
Question	coffeemonitor	View Question on Stackoverflow
Solution 1 - Mysql	ypercubeᵀᴹ	View Answer on Stackoverflow
Solution 2 - Mysql	Eugen Rieck	View Answer on Stackoverflow
Solution 3 - Mysql	Slawomir	View Answer on Stackoverflow
Solution 4 - Mysql	bucko	View Answer on Stackoverflow
Solution 5 - Mysql	PodTech.io	View Answer on Stackoverflow
Solution 6 - Mysql	Salvi Pascual	View Answer on Stackoverflow
Solution 7 - Mysql	Kevin Chen	View Answer on Stackoverflow

MySQL IF NOT NULL, then display 1, else display 0

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

Solution 5 - Mysql

Solution 6 - Mysql

Solution 7 - Mysql

Best practice for embedding arbitrary JSON in the DOM?

How can I implement static methods on an interface?

Attributions