Is there a way to only show first non-zero value but if all values are zero then show 1st instance of a column in a separate table from joining tables

Question

These two tables are quite large, and my select statement has more values I am obtaining, but I think I can simplify this data and query so my question can be answered.

Here is my select statement:

SELECT invoice.InvoiceNum, Layer, InvoiceItemNum 
FROM (INVOICE 
        left outer join InvoiceItem item 
            ON item.InvoiceNum = Invoice.InvoiceNum
    ) 
ORDER BY invoice.InvoiceNum

So I have two tables. An Invoice table and an InvoiceItem table. They are joined by the InvoiceNum column in each table, and displaying the InvoiceNum and Layer Column

Here is a result to this query:

InvoiceNum  | Layer | InvoiceItemNum
1           | 10    | 1
1           | 0     | 2
1           | 7     | 3 
1           | 0     | 4 
2           | 0     | 1
2           | 3     | 2 
3           | 0     | 1
3           | 0     | 2
3           | 0     | 3 
4           | 0     | 1
4           | 0     | 2
4           | 5     | 3

Since my InvoiceItem table has multiple rows that can be assigned to 1 InvoiceNum this is causing me to have duplicate invoiceNums in my result, which I do not want.

Here is the result I am trying to get, only listing 1 invoiceNum from the Invoice table, with the first case of a non-zero value from the InvoiceItem table's layer column, and If there is no non-zero than list the first zero.

Trying for something like this:

InvoiceNum  | Layer | InvoiceItemNum
1           | 10    | 1
2           | 3     | 2 
3           | 0     | 1
4           | 5     | 3

I am just not sure how to do this or if this is possible given that these are on two different tables.

Answer 1

this question is a bit tricky:

Try this in Postgres:

with cte as (
select 
inv.invoicenum,sum(layer::int) "sum_layer"
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
group by 1
)
,
cte1 as (

select distinct on (inv.invoicenum) inv.invoicenum,layer, InvoiceItemNum

from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
where inv.invoicenum in (select invoicenum from cte where sum_layer=0)
order by inv.invoicenum, InvoiceItemNum
),
cte2 as (
select 
distinct on (inv.invoicenum) inv.invoicenum, layer , InvoiceItemNum
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
where inv.invoicenum in (select invoicenum from cte where sum_layer>0) and layer::int>0
order by inv.invoicenum, InvoiceItemNum
)
(
select * from cte1
union all
select * from cte2
)
order by 1

DEMO

In MySQL 8:

Try This:

with cte as (
select 
inv.invoicenum,sum(layer) "sum_layer"
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
group by 1
)
,
cte1 as (
select * from (
select inv.invoicenum, layer, InvoiceItemNum
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
where inv.invoicenum in (select invoicenum from cte where sum_layer=0)
order by inv.invoicenum, InvoiceItemNum
) c
group by invoicenum
),
cte2 as (
select * from (
select inv.invoicenum, layer, InvoiceItemNum
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
where inv.invoicenum in (select invoicenum from cte where sum_layer>0) and layer>0
order by inv.invoicenum, InvoiceItemNum ) c
group by invoicenum
)
(
select * from cte1
union all
select * from cte2
)