1

Background: I am writing a query to compare actual and ideal growth data for children. Kids see doc once a year, doc measures, points get laid over typical growth curves. The problem is that the graphic component I use requires a fixed number of data points. No problem in having missing data as NULL, but they have to be there.

Data I've got:

age weight/kg
-----------------
2   10
3   11
6   20

Expected:

age weight/kg
-----------------
2   10
3   11
4   NULL
5   NULL
6   20

The required age values are fixed (2-6 in this example) and can be hard-coded, as they aren't in any table, otherwise I'd join and group. I would like to use a single query.

PeerBr
  • 675
  • 1
  • 11
  • 26
  • possible duplicate of [How to find gaps in sequential numbering in mysql?](http://stackoverflow.com/questions/4340793/how-to-find-gaps-in-sequential-numbering-in-mysql) – hampusohlsson Jan 15 '15 at 00:41
  • @hamohl, not quite. That question is about finding missing entries, mine is about merging incomplete sets to a complete sequence. – PeerBr Jan 15 '15 at 12:03

2 Answers2

2

If you want the query where a Sequence can be generated between 0 and 99, here is the query. You can get the weight from the Sub-Query which is in your table. I have given the range 2 to 6 here.

SELECT
    SEQ.SeqValue AS age,
    (SELECT weight FROM your_table WHERE age = SEQ.SeqValue) AS "weight/kg"
FROM
(
SELECT
    (TENS.SeqValue + ONES.SeqValue) SeqValue
FROM
    (
    SELECT 0  SeqValue
    UNION ALL
    SELECT 1 SeqValue
    UNION ALL
    SELECT 2 SeqValue
    UNION ALL
    SELECT 3 SeqValue
    UNION ALL
    SELECT 4 SeqValue
    UNION ALL
    SELECT 5 SeqValue
    UNION ALL
    SELECT 6 SeqValue
    UNION ALL
    SELECT 7 SeqValue
    UNION ALL
    SELECT 8 SeqValue
    UNION ALL
    SELECT 9 SeqValue
    ) ONES
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 10 SeqValue
    UNION ALL
    SELECT 20 SeqValue
    UNION ALL
    SELECT 30 SeqValue
    UNION ALL
    SELECT 40 SeqValue
    UNION ALL
    SELECT 50 SeqValue
    UNION ALL
    SELECT 60 SeqValue
    UNION ALL
    SELECT 70 SeqValue
    UNION ALL
    SELECT 80 SeqValue
    UNION ALL
    SELECT 90 SeqValue
    ) TENS
) SEQ
WHERE SEQ.SeqValue BETWEEN 2 AND 6
Keshan Fernando
  • 347
  • 1
  • 4
  • 16
  • Thank you, that was brilliant. Added `ORDER BY age ASC` and good to go. Learned a lot from your answer, thanks again. – PeerBr Jan 15 '15 at 12:01
0

Use IFNULL() in your query to assign it a value of zero http://www.mysqltutorial.org/mysql-ifnull/

Michael St Clair
  • 5,937
  • 10
  • 49
  • 75
  • Thanks, but the ifnull() part is the least of my concerns. The main problem is a union of mixed and fixed results. – PeerBr Jan 15 '15 at 09:41