MySQL: organizing result sets to get complete and unique data

Other Tech Topics
1

Hello,

I have the following database:
CREATE TABLE sectors (sec_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, sec_name CHAR(25));

INSERT INTO sectors (sec_id, sec_name) VALUES (NULL, 'Spinward Marches');

CREATE TABLE subsectors (sub_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, sec_home INT, sub_name CHAR(25));

INSERT INTO subsectors (sub_id, sec_home, sub_name) VALUES (NULL, 1, 'Querion');

CREATE TABLE worlds (wld_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, sec_home INT, subsec_home INT, name CHAR(25), location Char(4), uwp CHAR(8), trade CHAR(15), cap CHAR(3), ex_uwp CHAR(3), alleg CHAR(2) );

CREATE TABLE stars ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, star_home INT, star_num INT, type CHAR(1), spectral INT, size Char(4) );

CREATE TABLE bases ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, base_home INT, base CHAR(3) );

INSERT INTO worlds (wld_id, sec_home, subsec_home, name, location, uwp, trade, cap, ex_uwp, alleg) VALUES (NULL, (SELECT sec_id FROM sectors WHERE sec_name='Spinward Marches'), (SELECT sub_id FROM subsectors WHERE sub_name='Chronor'),'Zeycude', '0101', 'C3306989', '000400000ABC000', NULL, '613', 'Zh' );
INSERT INTO stars (id, star_home, star_num, type, spectral, size) VALUES ( NULL, (SELECT wld_id FROM worlds WHERE location='0101'), 1, 'K', '9', 'V' ); INSERT INTO bases (id, base_home, base) VALUES ( NULL, (SELECT wld_id FROM worlds WHERE location='0101'), 'I' ); INSERT INTO bases (id, base_home, base) VALUES ( NULL, (SELECT wld_id FROM worlds WHERE location='0101'), 'Z' );

INSERT INTO worlds (wld_id, sec_home, subsec_home, name, location, uwp, trade, cap, ex_uwp, alleg)
VALUES (NULL, (SELECT sec_id FROM sectors WHERE sec_name='Spinward Marches'), (SELECT sub_id FROM subsectors WHERE sub_name='Chronor'), 'Reno', '0102', 'C1207B9A', '000400000AB0000', NULL, '603', 'Zh' );
INSERT INTO stars (id, star_home, star_num, type, spectral, size) VALUES ( NULL, (SELECT wld_id FROM worlds WHERE location='0102'), 1, 'G', '8', 'V' );
INSERT INTO stars (id, star_home, star_num, type, spectral, size) VALUES ( NULL, (SELECT wld_id FROM worlds WHERE location='0102'), 2, 'M', '1', 'D' );
INSERT INTO bases (id, base_home, base) VALUES ( NULL, (SELECT wld_id FROM worlds WHERE location='0102'), NULL );

When I use the select:
SELECT sec_name, sub_name, location, name, uwp, base, trade, cap, ex_uwp, alleg, type, spectral, size FROM sectors s JOIN subsectors u ON s.sec_id=u.sec_home JOIN worlds w ON w.sec_home=s.sec_id JOIN stars t ON t.star_home=wld_id JOIN bases b ON b.base_home=wld_id WHERE sub_name='Chronor';

I get:

| sec_name | sub_name | location | name | uwp | base | trade | cap | ex_uwp | alleg | type | spectral | size |
+------------------+----------+----------+---------+----------+------+-----------------+------+--------+-------+------+----------+------+
| Spinward Marches | Chronor | 0101 | Zeycude | C3306989 | I | 000400000ABC000 | NULL | 613 | Zh | K | 9 | V |
| Spinward Marches | Chronor | 0101 | Zeycude | C3306989 | Z | 000400000ABC000 | NULL | 613 | Zh | K | 9 | V |
| Spinward Marches | Chronor | 0102 | Reno | C1207B9A | NULL | 000400000AB0000 | NULL | 603 | Zh | G | 8 | V |
| Spinward Marches | Chronor | 0102 | Reno | C1207B9A | NULL | 000400000AB0000 | NULL | 603 | Zh | M | 1 | D

What I'd like is:

| sec_name | sub_name | location | name | uwp | base | base 2(if exist) | trade | cap | ex_uwp | alleg | type | spectral | size | type 2(if exist) | spectral 2(if exist) | size 2(if exist) |
+------------------+----------+----------+---------+----------+------+-----------------+------+--------+-------+------+----------+------+
**| Spinward Marches | Chronor | 0101 | Zeycude | C3306989 | I | Z | **
000400000ABC000 | NULL | 613 | Zh | K | 9 | V |
| Spinward Marches | Chronor | 0102 | Reno | C1207B9A | NULL | 000400000AB0000 | NULL | 603 | Zh | G | 8 | V | M | 1 | D

But I can't figure the SQL.
Can someone help me?

Thanks in advance!

2

I'm confused as to what you're looking for. Correct me if I am wrong but it seems, from the result set you mentioned at the bottom, you want to create new, additional, columns when grouping those entries. For example, from the result set you mentioned you are looking for, the Zeycude entry has 14 columns whereas the Reno entry has 16 columns.

3

What I'm after is to re-organize the result set.
In the initial result set, a single row is produced for each occurrence of "base" in the database.

As well, a single row is produced for each occurrence set of "Type" "spectral" and "Size" in the database.

What I'd "Like" to do is produce one row per "name".
So, rather than two rows each indicating one base, I'd like one row where the base column occurs for each base in the database where b.base_home = wld_id

As well, I'd like that one row to have appended to the end each set of "Type" "spectral" and "Size"
where t.star_home = wld_id

4

Thank you for clarifying. It sounds like you'll want to work with ROW_NUMBER(). This will allow you to identify if there are any duplicate occurances of name, and make the appropriate allocations within the result set. My only concern is with 3 instances of the same name. This query is not optimized to handle that and would simply omit the 3rd result.

There may be a more efficient way of handling this. Please correct me if there is.

SELECT
    CASE
        WHEN info.rowNum = 1
            THEN info.sec_name
    END AS 'sec_name'
    ,CASE
        WHEN info.rowNum = 1
            THEN info.sub_name
    END AS 'sub_name'
    ,CASE
        WHEN info.rowNum = 1
            THEN info.location
    END AS 'location'
    ,CASE
        WHEN info.rowNum = 1
            THEN info.name
    END AS 'name'
    ,CASE
        WHEN info.rowNum = 1
            THEN info.uwp
    END AS 'uwp'
    ,CASE
        WHEN info.rowNum = 1
            THEN info.base
    END AS 'base'
    ,CASE
        WHEN info.rowNum = 2
            THEN info.base
    END AS 'base2'
    ,CASE
        WHEN info.rowNum = 1
            THEN info.trade
    END AS 'trade'
    ,CASE
        WHEN info.rowNum = 1
            THEN info.cap
    END AS 'cap'
    ,CASE
        WHEN info.rowNum = 1
            THEN info.ex_uwp
    END AS 'ex_uwp'
    ,CASE
        WHEN info.rowNum = 1
            THEN info.alleg
    END 'alleg'
    ,CASE
        WHEN info.rowNum = 1
            THEN info.type
    END 'type'
    ,CASE
        WHEN info.rowNum = 1
            THEN info.spectral
    END AS 'spectral'
    ,CASE
        WHEN info.rowNum = 1
            THEN info.size
    END AS 'size'
    ,CASE
        WHEN info.rowNum = 2
            THEN info.type
    END AS 'type2'
    ,CASE
        WHEN info.rowNum = 2
            THEN info.spectral
    END AS 'spectral2'
    ,CASE
        WHEN info.rowNum = 2
            THEN info.size
    END AS 'size2'
FROM (
    SELECT 
        tbl.sec_name
        ,tbl.sub_name
        ,tbl.location
        ,tbl.name
        ,tbl.uwp
        ,tbl.base
        ,tbl.trade
        ,tbl.cap
        ,tbl.ex_uwp
        ,tbl.alleg
        ,tbl.type
        ,tbl.spectral
        ,tbl.size
        ,ROW_NUMBER() OVER(PARTITION BY tbl.name
                           ORDER BY tbl.location DESC) rowNum
    FROM 
        sectors s 
        JOIN subsectors u 
            ON s.sec_id=u.sec_home 
        JOIN worlds w 
            ON w.sec_home=s.sec_id 
        JOIN stars t 
            ON t.star_home=wld_id 
        JOIN bases b 
            ON b.base_home=wld_id
    GROUP BY 
        tbl.sec_name
        ,tbl.sub_name
        ,tbl.location
        ,tbl.name
        ,tbl.uwp
        ,tbl.base
        ,tbl.trade
        ,tbl.cap
        ,tbl.ex_uwp
        ,tbl.alleg
        ,tbl.type
        ,tbl.spectral
        ,tbl.size
) info
WHERE 
    info.sub_name='Chronor';
5

I belive row_number function is not available in mysql

6

My apologies for the delay, but I was surprised by a project and only just resolved it.

I attempted the solution above, and I thank devAdvcte for the attempt.
But I got the following:
"ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY
tbl.name
ORDER BY tbl.location DESC) ro' at line 85"

Line 85, 86 and 87 are:
,ROW_NUMBER() OVER(PARTITION BY tbl.name
ORDER BY tbl.location DESC) rowNum
FROM