SQL Help: 3 Tables into 1 Line-CodePudding

I have three (3) tables that are able to be linked: Sales Orders, Line Items and Products

I want to create a query that shows for each sales order the relevant product types based on it's line items. Every sales order will contain a sensor model and tablet model that are embedded into the Product table (which is linked to the line items table). I have this, but know its wrong:

SELECT DISTINCT
         "Sales Orders"."id" as "SOID",
         "Product Category" = if("Products"."SubType2"  like '%Sensors%', "Products"."Product Category", ''),
         "Sensor" = if("Products"."SubType2"  like '%Sensors%', "Products"."SubType3", ''),
         "Tablet" = if("Products"."SubType2"  like '%Tablets%', "Products"."SubType3", '')
FROM  "Sales Orders"
LEFT JOIN "Ordered Items" ON "Sales Orders"."id"  = "Ordered Items"."Parent ID" 
INNER JOIN "Products" ON "Ordered Items"."Product Name"  = "Products"."id"  
GROUP BY "SOID",
     "Product Category",
     "Sensor",
     "Tablet"

It doesn't error out, but show multiple rows for the sales order with the relevant sensor and tablet, but not in the same row.

it produces:

{2532096000123156204, Positioning, RTS873, }
{2532096000123156204,,,T100}

What I want is:

{2532096000123156204, Positioning, RTS873, T100 }

CodePudding user response：

Welcome to S/O. Few quick items. Showing sample data helps even if you edit your existing question and paste text in directly and use spaces for alignment and block quote it with the { } icon above editing area.

Readability of SQL can be improved by using "alias" references, especially when using tables and/or columns named with embedded spaces (not cool for long-term maintenance, IMO). So, where you had "Sales Order", I put "so" as the alias to the table, so the rest of the query can use that as shorthand vs retyping the long name over and over. Similarly with "Ordered Items" having alias "oi". So, the only time you should really need to use the double-quotes is with cases of embedded spaces in table/columns.

Now, on to your query. You did Distinct which is close, but what I think you really need is what I provided. By doing a group by the sales order ID, it would only return one ID. Since you have multiple possible line items, I applied a MAX() aggregate. So, when it is on the first line item that is a Sensor, only then will it get the SubType2 from products table, any other time, defaults to empty string, so the MAX() will only return a value IF it is found. Similarly for all 3 Product Category, Sensor and Tablet columns.

SELECT 
        so.id SOID,
        so.Subject,
        MAX( CASE when p.SubType2 like '%Sensors%'
            then p."Product Category" else '' end ) "Product Category",
        MAX( case when p.SubType2 like '%Sensors%'
            then p.SubType3 else '' end ) "Sensor",
        MAX( case when p.SubType2 like '%Tablets%'
            then p.SubType3 else '' end ) "Tablet"
    FROM
        "Sales Orders" so
            LEFT JOIN "Ordered Items" oi
                ON so.id  = oi."Parent ID"
                INNER JOIN Products p
                    ON oi."Product Name"  = p.id
    GROUP BY 
        so.id,
        so.Subject