Select all Main table rows with detail table column constraints with GROUP BY

Question

I've 2 tables tblMain and tblDetail on SQL Server that are linked with tblMain.id=tblDetail.OrderID for orders usage. I've not found exactly the same situation in StackOverflow.

Here below is the sample table design:

/* create and populate tblMain: */
CREATE TABLE tblMain (
    ID int IDENTITY(1,1) NOT NULL,
    DateOrder datetime NULL,
  CONSTRAINT PK_tblMain PRIMARY KEY
  (
      ID ASC
  )
)
GO

INSERT INTO tblMain (DateOrder) VALUES('2021-05-20T12:12:10');
INSERT INTO tblMain (DateOrder) VALUES('2021-05-21T09:13:13');
INSERT INTO tblMain (DateOrder) VALUES('2021-05-22T21:30:28');
GO

/* create and populate tblDetail: */
CREATE TABLE tblDetail (
    ID int IDENTITY(1,1) NOT NULL,
    OrderID int NULL,
    Gencod VARCHAR(255),
  Quantity float,
    Price float,
  CONSTRAINT PK_tblDetail PRIMARY KEY
  (
      ID ASC
  )
)
GO

INSERT INTO tblDetail (OrderID, Gencod, Quantity, Price) VALUES(1, '1234567890123', 8, 12.30);
INSERT INTO tblDetail (OrderID, Gencod, Quantity, Price) VALUES(1, '5825867890321', 2, 2.88);
INSERT INTO tblDetail (OrderID, Gencod, Quantity, Price) VALUES(3, '7788997890333', 1, 1.77);
INSERT INTO tblDetail (OrderID, Gencod, Quantity, Price) VALUES(3, '9882254656215', 3, 5.66);
INSERT INTO tblDetail (OrderID, Gencod, Quantity, Price) VALUES(3, '9665464654654', 4, 10.64);
GO

Here is my SELECT with grouping:

SELECT tblMain.id,SUM(tblDetail.Quantity*tblDetail.Price) AS TotalPrice
  FROM tblMain LEFT JOIN tblDetail ON tblMain.id=tblDetail.orderid
  WHERE (tblDetail.Quantity<>0) GROUP BY tblMain.id;
GO

This gives:

The wished output:

We see that id=2 is not shown even with LEFT JOIN, as there is no records with OrderID=2 in tblDetail.

How to design a new query to show tblMain.id = 2? Mean while I must keep WHERE (tblDetail.Quantity<>0) constraints. Many thanks.

EDIT:

The above query serves as CTE (Common Table Expression) for a main query that takes into account payments table tblPayments again.

After testing, both solutions work.

In my case, the main table has 15K records, while detail table has some millions. With (tblDetail.Quantity<>0 OR tblDetail.Quantity IS NULL) AND tblDetail.IsActive=1 added on JOIN ON clause it takes 37s to run, while the first solution of @pwilcox, the condition being added on the where clause, it ends up on 29s. So a gain of time of 20%.

tblDetail.IsActive column permits me ignore detail rows that is temporarily ignored by setting it to false.

So the for me it's ( @pwilcox's answer).

where (tblDetail.quantity <> 0 or tblDetail.quantity is null)

There is a more important lesson to learn here. When you refer to a column of the unpreserved table (tblDetail) in the WHERE clause, you implicitly convert the outer join to an inner join. The alternative change is to move the filter into join clause. But why would Quantity be zero? That seems like a logic flaw. And since you are summing, a quantity of zero does not change any sum so why bother? — SMor, May 21 '21 at 23:25
tblDetail.Quantity <> 0 is an example, I've also tblDetail.IsActive that must be true also to select only products that is active... — jacouh, May 21 '21 at 23:36
Does this answer your question? [Why is my t-sql left join not working?](https://stackoverflow.com/questions/40093809/why-is-my-t-sql-left-join-not-working) — Charlieface, May 23 '21 at 00:52

pwilcox · Accepted Answer · 2021-05-22T01:40:08.713

1

Change

WHERE (tblDetail.Quantity<>0)

to

where (tblDetail.quantity <> 0 or tblDetail.quantity is null)

as the former will omit id = 2 because the corresponding quantity would be null in a left join.

And as HABO mentions, you can also make the condition a part of your join logic as opposed to your where statement, avoiding the need for the 'or' condition.

select    m.id,
          totalPrice = sum(d.quantity * d.price) 
from      tblMain m 
left join tblDetail d 
            on m.id = d.orderid
            and d.quantity <> 0
group by  m.id;

edited May 22 '21 at 01:40

answered May 21 '21 at 23:08

pwilcox

5,542
1
19
31

1

@jacouh Better yet, move it to `... on tblMain.id = tblDetail.orderid and tblDetail.quantity <> 0` where nulls are expected. It really is part of the `join` conditions. – HABO May 22 '21 at 01:33
@HABO, I'll give you ten minutes or so to write that up as your own post if you want. After that I'll just add it as an edit to my answer so that it gets promoted to main content. – pwilcox May 22 '21 at 01:35
Ah, wait, I think in this situation a community wiki might work best. – pwilcox May 22 '21 at 01:36

Select all Main table rows with detail table column constraints with GROUP BY

1 Answers1