StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGROUP_CONCAT pulling out empty result with delimiters
text
Body
copied!<p>I asked a question yesterday on how to pull out multiple results into one field and was given the answer <code>GROUP_CONTACT()</code>.</p> <p>I've put this into my code and it works fine. I need to do this for two fields and thus I have now started to use it twice in the same sql statement. Unfortunately, it's pulling back an empty list for the second field with commas and I'm not too sure why.</p> <p>Here is my sample product data:</p> <pre><code>pid || prod 1 || top 2 || sweater </code></pre> <p>Here is my sample stock data (some stock doesn't have two sizes e.g. waist and chest):</p> <pre><code>sid || size1 || size2 || pid 1 || M || || 1 2 || L || || 1 3 || XL || || 1 4 || L || || 2 5 || XL || || 2 </code></pre> <p>Here is my code:</p> <pre><code>SELECT p.id, GROUP_CONCAT(s.size1) size1, GROUP_CONCAT(s.size2) size2, p.prod FROM products p JOIN stock s ON s.prodid = p.id </code></pre> <p>This is what it should bring out:</p> <pre><code>pid || size1 || size2 || prod 1 || M,L,XL || || top 2 || L,XL || || sweater </code></pre> <p>This is what it is actually bringing out:</p> <pre><code>pid || size1 || size2 || prod 1 || M,L,XL || ,, || top 2 || L,XL || , || sweater </code></pre> <p>I've checked to see if there is a space or anything in size2 and there is nothing in there.</p> <p>I did this query and the product came back as I expected:</p> <pre><code>SELECT size1, size2 FROM stock WHERE pid = 1 AND size2 = "" </code></pre> <p>When I did this query, nothing came back:</p> <pre><code>SELECT size1, size2 FROM stock WHERE pid = 1 AND size2 IS NULL </code></pre> <p>I know <code>GROUP_CONCAT()</code> will ignore NULL results but I need to do something to stop <code>GROUP_CONTACT()</code> from showing an empty comma delimited list when it is just <code>""</code> rather than <code>NULL</code>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload