0

Scenario - a sailing event with entrant categories.

I am a volunteer and I am struggling to get a result table for each entrant listing their selection (or NULL if none chosen) for each category. Please help me!

Entrant 2097 has values set for all 3 categories,
entrant 2099 only two categories (no crew choice made)
BUT I need a row with NULL value for Crew.

The output rows I need are shown below
(i.e. MUST include the final row with ecnName = 'Crew', ecyEntry='2099' and eciValue = 'NULL')
and my query does not do that!

+---------+----------+----------+
| ecnName | eciValue | ecyEntry |
+---------+----------+----------+
| Fleet   | Gold     |     2097 |
| Helm    | Male     |     2097 |
| Crew    | Male     |     2097 |
| Fleet   | Silver   |     2099 |
| Helm    | Male     |     2099 |
| Crew    | NULL     |     2099 |
+---------+----------+----------+

I have tried various combinations of LEFT/RIGHT joins,
but not got the answer, so any guidance really appreciated!

My query is below, and the source tables.

My current query:

[racescoring]>
SELECT *
FROM `tblcategories`
JOIN `tblcategoryitems`
  ON eciCategory = ecnID
LEFT JOIN tblentrycategories
  ON ecyItem = eciID
WHERE ecnEvent = 57
ORDER BY ecyEntry, ecyItem;

gives result which includes rows that have no entrant choices at all, and NOT a null for entrant 2099 Crew, so NOT what I need!

+-------+----------+----------+---------+-------+-------------+----------+----------+---------+
| ecnID | ecnEvent | ecnOrder | ecnName | eciID | eciCategory | eciValue | ecyEntry | ecyItem |
+-------+----------+----------+---------+-------+-------------+----------+----------+---------+
|    32 |       57 |        3 | Crew    |    61 |          32 | Female   |     NULL |    NULL |
|    31 |       57 |        2 | Helm    |    59 |          31 | Female   |     NULL |    NULL |
|    29 |       57 |        1 | Fleet   |    55 |          29 | Bronze   |     NULL |    NULL |
|    29 |       57 |        1 | Fleet   |    53 |          29 | Gold     |     2097 |      53 |
|    31 |       57 |        2 | Helm    |    58 |          31 | Male     |     2097 |      58 |
|    32 |       57 |        3 | Crew    |    60 |          32 | Male     |     2097 |      60 |
|    29 |       57 |        1 | Fleet   |    54 |          29 | Silver   |     2099 |      54 |
|    31 |       57 |        2 | Helm    |    58 |          31 | Male     |     2099 |      58 |
+-------+----------+----------+---------+-------+-------------+----------+----------+---------+

There are 3 categories for this particular event id=57.

[racescoring]> SELECT * FROM tblcategories;
+-------+----------+----------+---------+
| ecnID | ecnEvent | ecnOrder | ecnName |
+-------+----------+----------+---------+
|    29 |       57 |        1 | Fleet   |
|    31 |       57 |        2 | Helm    |
|    32 |       57 |        3 | Crew    |
+-------+----------+----------+---------+

Each category has possible item values in this table

[racescoring]> SELECT * FROM tblcategoryitems;
+-------+-------------+----------+
| eciID | eciCategory | eciValue |
+-------+-------------+----------+
|    55 |          29 | Bronze   |
|    53 |          29 | Gold     |
|    54 |          29 | Silver   |
|    59 |          31 | Female   |
|    58 |          31 | Male     |
|    61 |          32 | Female   |
|    60 |          32 | Male     |
+-------+-------------+----------+

Testing with entrycategories for just two sailors.

[racescoring]> SELECT * FROM tblentrycategories;
+----------+---------+
| ecyEntry | ecyItem |
+----------+---------+
|     2097 |      53 |
|     2097 |      58 |
|     2097 |      60 |
|     2099 |      54 |
|     2099 |      58 |
+----------+---------+
0

4 Answers 4

0

basically (removing un-needed columns) the output I need is the below (i.e. with a NULL for eciValue in the final row)

To remove unneeded columns, you should list needed:

SELECT  ecnName, eciValue, ecyEntry
FROM `tblcategories` 
JOIN `tblcategoryitems` ON eciCategory = ecnID 
LEFT JOIN tblentrycategories ON ecyItem = eciID 
WHERE ecnEvent = 57 
ORDER BY ecyEntry, ecyItem;

The difference between select * and select a,b,c is described in each and every textbook on SQL.

1
  • White Owl - thank you, but that does not answer my question. (I know how to remove unneeded columns). As the post title says, the issue is the rows returned are not what I want. The first table in my post shows what I finally want, the second table the query and what I am getting. My question is what the query should be to get the result rows I want .
    – Chris H
    Jun 6, 2023 at 15:44
0

Your data is not really modelled correctly for how your races work in real life.
Specifically the optional Crew logic.

However, if we really hack the data around by joining and grouping in weird ways, you can get the "right" output that you asked for.

Only problem is that I don't have MySQL, so here is a solution in Microsoft SQL:

SELECT ecnName, eciValue,ecyEntry
FROM (
    SELECT ecnOrder,ecnName, eciValue,ecyEntry
        ,ROW_NUMBER() OVER (
             PARTITION BY ecnOrder,ecnName,ecyEntry
             ORDER BY eciValue DESC) AS MagicRow
    FROM (VALUES
         ( 29, 57, 1, 'Fleet')
        ,( 31, 57, 2, 'Helm' ) 
        ,( 32, 57, 3, 'Crew' )
        ) AS Categories (ecnID, ecnEvent, ecnOrder, ecnName)
    CROSS JOIN (
        SELECT *
        FROM (VALUES
             (2097, 53)
            ,(2097, 58)
            ,(2097, 60)
            ,(2099, 54)
            ,(2099, 58)
        )x(ecyEntry, ecyItem)
    ) AS Entries
    LEFT JOIN (
        SELECT *
        FROM (VALUES
             (55, 29, 'Bronze')
            ,(53, 29, 'Gold'  )
            ,(54, 29, 'Silver')
            ,(59, 31, 'Female')
            ,(58, 31, 'Male'  )
            ,(61, 32, 'Female')
            ,(60, 32, 'Male'  )
        )x(eciID, eciCategory, eciValue)
    ) AS Items
        ON (Items.eciCategory = Categories.ecnID
        AND Entries.ecyItem = Items.eciID)
) Results
WHERE MagicRow = 1
ORDER BY ecyEntry,ecnOrder
/*
Actual data:

ecnName eciValue ecyEntry
Fleet   Gold     2097
Helm    Male     2097
Crew    Male     2097
Fleet   Silver   2099
Helm    Male     2099
Crew    NULL     2099

Desired Output (makes no sense!):

+---------+----------+----------+
| ecnName | eciValue | ecyEntry |
+---------+----------+----------+
| Fleet   | Gold     |     2097 |
| Helm    | Male     |     2097 |
| Crew    | Male     |     2097 |
| Fleet   | Silver   |     2099 |
| Helm    | Male     |     2099 |
| Crew    | NULL     |     2099 |
+---------+----------+----------+
*/
3
  • Thank you, I will give it a try. In "real life" the boats may be a mixture of single-handed and double-handed, so crew may not apply to all. Also note the categories and choices may be different for different events, above is an example only to get logic working
    – Chris H
    Jun 6, 2023 at 16:31
  • I suspect subqueries are the way to go, but the above will not work with the much bigger actual tables
    – Chris H
    Jun 6, 2023 at 17:38
  • The subqueries were just a method to get your example data into a workable form for prototyping some code. The meat of the solution is in the CROSS JOIN, LEFT JOIN and the Grouping/Sorting of the MagicRow.
    – Oreo
    Jun 7, 2023 at 0:00
0

I reworked this answer based on your feedback. I also note that you have posted your own answer. I leave this answer for the benefit of future visitors.


It's possible that MySQL is applying the LEFT JOIN before it applies the restriction to event 57. In that event, if the sailor in question is entered in some other event, and that event has specified a crew, you will see the wrong answer you got.

The following rework of your query is intended to force MySQL to restrict the items to event 57 BEFORE it does the left join. I don't know MySQL, so there could be syntax errors here.

SELECT * 
    FROM tblcategoryitems 
    JOIN tblcategories ON eciCategory = ecnID 
    LEFT JOIN tblentrycategories 
        ON (ecyItem = eciID AND ecnEvent = 57) 
    ORDER BY ecyEntry, ecyItem;
1
  • Thanks for reply. But it gives the same output as in my original question (i.e. 3 null rows at top and NO row for 2099 Crew
    – Chris H
    Jun 7, 2023 at 14:04
0

In case this helps anyone else I have finally achieved a result using a UNION statement.

First part selects the 'missing' item for the final row: 2099 | 3 | Crew | |.
Second part selects all the rows with values that DO exist.
Also a bit of column aliasing to align the two parts.

SELECT entID as entryID, ecnOrder, ecnName,' ' AS Item /* using space, not NULL */
FROM `tblentries`
JOIN `tblCategories` ON ecnEvent = entEvent
WHERE (
    entEvent = ecnEvent
    AND (entID, ecnID) NOT IN (
        SELECT ecyEntry, eciCategory
        FROM `tblentryCategories`
        JOIN `tblcategoryitems` ON eciID = ecyItem
        JOIN `tblEntries` ON ecyEntry = entID
        )
    )
    AND entEvent = 57

UNION

SELECT ecyEntry as entryID, ecnOrder, ecnName, eciValue as Item
FROM `tblcategories` 
JOIN `tblcategoryitems` ON eciCategory = ecnID
JOIN `tblentrycategories` ON ecyItem = eciID
WHERE ecnEvent = 57
  AND ecyItem = eciID
ORDER BY entryID,ecnOrder,Item;

Query result:

+---------+----------+---------+--------+
| entryID | ecnOrder | ecnName | Item   |
+---------+----------+---------+--------+
|    2097 |        1 | Fleet   | Gold   |
|    2097 |        2 | Helm    | Male   |
|    2097 |        3 | Crew    | Male   |
|    2099 |        1 | Fleet   | Silver |
|    2099 |        2 | Helm    | Male   |
|    2099 |        3 | Crew    |        |
+---------+----------+---------+--------+
2
  • Thanks Oreo for tidying my answer, it IS more readable :)
    – Chris H
    Jun 11, 2023 at 12:57
  • Your answer contains references to a table that's never mentioned in your question (tblentries). If your solution indeed represents an optimal way to resolve the issue, it seems like you made it impossible for the audience to find it for you by inadvertently withholding information. Congrats on finding your answer on your own nevertheless.
    – Andriy M
    Jun 11, 2023 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.