select -group

Question

select -group

arkiboys 9,711

Hello,
I would like to carry out rules on a sample table as below.
Can you see how to return the final result please?
Thank you

create table #tblMain
(
V_Number varchar(50),m_name varchar(50),party varchar(50),m_shipment varchar(50),[Name] varchar(50)
,estimated date,actual date,ml_name varchar(50),Doc_Name varchar(50),[weight] float
)

insert into #tblMain (V_Number,m_name,party,m_shipment,[Name],estimated,actual,ml_name,Doc_Name,[weight])
values ('V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','t DateName','7/1/2021','7/1/2021','destination','uk',33.2),
 ('V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','t DateName','7/1/2021','7/1/2021','source','france',33.2),
 ('V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','l DateName','7/1/2021','7/1/2021','source','uk',33.2),
 ('V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','l DateName','7/1/2021','7/1/2021','destination','scotland',33.2),
 ('V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','v DateName','7/30/2021',null,'source','scotland',33.2),
 ('V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','v DateName','7/30/2021',null,'destination','belgium',33.2),
 ('V_98xx','company999','partyNamexxx','SHIP_xyz','t DateName','7/1/2021','7/2/2021','destination','uk',40.12),
 ('V_98xx','company999','partyNamexxx','SHIP_xyz','t DateName','7/1/2021','7/2/2021','source','france',40.12),
 ('V_98xx','company999','partyNamexxx','SHIP_xyz','l DateName','7/1/2021','7/2/2021','source','uk',40.12),
 ('V_98xx','company999','partyNamexxx','SHIP_xyz','l DateName','7/1/2021','7/2/2021','destination','scotland',40.12),
 ('V_98xx','company999','partyNamexxx','SHIP_xyz','v DateName','7/30/2021','01/8/2021','destination','belgium',40.12),
 ('V_98xx','company999','partyNamexxx','SHIP_xyz','v DateName','7/30/2021','01/8/2021','source','scotland',40.12)

select * from #tblMain
--drop table #tblMain
/*
select V_Number, m_name from the #tblMain where [RULE as below] group by V_Number, m_name

check rule:
if
[actual] date column of (t DateName) row values in [Name] column is not null
and [actual] date column of (l DateName) row values is not null
and [actual] date column of (v DateName) rows values is not null
and weight is not null

--The above rule should return:
V_Number, m_name
----------------
V_98xx companyNamexyz
*/

0 comments

Answer accepted by question author

Erland Sommarskog 134.7K MVP Volunteer Moderator

It would have helped to have some sample data where my previous query gave the wrong result. But here is my next guess:

SELECT A.V_Number, A.m_name
 FROM   (SELECT DISTINCT V_Number, m_name FROM #tblMain) AS A
 WHERE  EXISTS (SELECT *
                FROM   #tblMain B
                WHERE  B.V_Number = A.V_Number
                  AND  B.m_name   = A.m_name
                  AND  B.actual IS NOT NULL 
                  AND  B.Name = 't DateName')
   AND  EXISTS (SELECT *
                FROM   #tblMain B
                WHERE  B.V_Number = A.V_Number
                  AND  B.m_name   = A.m_name
                  AND  B.actual IS NULL 
                  AND  B.Name IN ('v DateName', 'l DateName'))

I note that in this round of the game the column weight has fallen out of the rules.

arkiboys 9,711 Reputation points

2022-05-07T20:03:04.157+00:00
this is what I have. what do you think?
In addition, the weight is still there.

select
distinct
--v1.V_Number, v1.m_name, v1.actual, v1.ml_name, v1.Name, v1.Doc_Name,
v2.V_Number, v2.m_name, v2.actual, v2.ml_name, v2.Name, v2.Doc_Name, v3.V_Number
from

tblMain as v1

inner join #tblMain as v2 on v1.V_Number = v2.V_Number and v1.m_name = v2.m_name inner join #tblMain as v3 on v1.V_Number = v3.V_Number and v1.m_name = v3.m_name

where
(v1.Name = 't DateName' and v1.actual is not null)
and
(
(v2.Name = 'v DateName' and v2.actual is null)
OR (v3.Name = 'l DateName' and v3.actual is null)
)
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-05-07T20:11:05.86+00:00

What I think? Well, in your original post you only wanted to columns back, here you return a lot more.

Anyway, if your query returns the correct result on your full data set, I guess you are happy. And if it does not, maybe my query does?

And if it does not, I think we need more sample data with more groups. Unfortunately, you explanation of the rules is someone terse, and difficult to understand. Bear in mind that we have no knowledge of the actual business problem you are trying to use. So it becomes a bit of a guessing game.

I don't think our queries are that much different, though.
arkiboys 9,711 Reputation points

2022-05-07T20:24:16.077+00:00
Your query helped and I converted it to below.
They both give the same results.
Which one do you prefer and why?
Thank you

select
distinct t1.V_Number, t1.m_name
from

tblMain as t1

inner join #tblMain as t2 on t1.V_Number = t2.V_Number and t1.m_name = t2.m_name

where
(t1.actual is not null and t1.[Name] = 't DateName')
and (t2.actual is null and t2.[Name] in ('l DateName', 'v DateName'))
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-05-07T20:30:28.037+00:00

I prefer my version. I've noticed that people often use join, probably they are more accustomed to that, and I surely did the same when I was less experienced with SQL.

But using joins when all you want to do is to check for existence, can easily lead to unwanted duplicates. This is also testified in your query by the DISTINCT. For a query like this one, you quick notice the duplicates, but it is more devilish if you have a GROUP BY query, where your SUM or other aggregates may be inflated, but you may not realise that the result is incorrect.

If you would look at the query plan for my query, you would probably see something about Semi-Join, and that is really what the EXISTS subquery is: a semi-join in relational algebra. So it is not different from your query, but it express the intent better, I think.
arkiboys 9,711 Reputation points

2022-05-07T20:36:27.783+00:00

I see.
I will be doing more tests and feed-back

Thank you

1 additional answer

Your answer

arkiboys 9,711 Reputation points

2022-05-07T20:03:04.157+00:00

this is what I have. what do you think?
In addition, the weight is still there.

select
distinct
--v1.V_Number, v1.m_name, v1.actual, v1.ml_name, v1.Name, v1.Doc_Name,
v2.V_Number, v2.m_name, v2.actual, v2.ml_name, v2.Name, v2.Doc_Name, v3.V_Number
from

tblMain as v1

inner join #tblMain as v2 on v1.V_Number = v2.V_Number and v1.m_name = v2.m_name inner join #tblMain as v3 on v1.V_Number = v3.V_Number and v1.m_name = v3.m_name

where
(v1.Name = 't DateName' and v1.actual is not null)
and
(
(v2.Name = 'v DateName' and v2.actual is null)
OR (v3.Name = 'l DateName' and v3.actual is null)
)
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-05-07T20:11:05.86+00:00

What I think? Well, in your original post you only wanted to columns back, here you return a lot more.

Anyway, if your query returns the correct result on your full data set, I guess you are happy. And if it does not, maybe my query does?

And if it does not, I think we need more sample data with more groups. Unfortunately, you explanation of the rules is someone terse, and difficult to understand. Bear in mind that we have no knowledge of the actual business problem you are trying to use. So it becomes a bit of a guessing game.

I don't think our queries are that much different, though.
arkiboys 9,711 Reputation points

2022-05-07T20:24:16.077+00:00

Your query helped and I converted it to below.
They both give the same results.
Which one do you prefer and why?
Thank you

select
distinct t1.V_Number, t1.m_name
from

tblMain as t1

inner join #tblMain as t2 on t1.V_Number = t2.V_Number and t1.m_name = t2.m_name

where
(t1.actual is not null and t1.[Name] = 't DateName')
and (t2.actual is null and t2.[Name] in ('l DateName', 'v DateName'))
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-05-07T20:30:28.037+00:00

I prefer my version. I've noticed that people often use join, probably they are more accustomed to that, and I surely did the same when I was less experienced with SQL.

But using joins when all you want to do is to check for existence, can easily lead to unwanted duplicates. This is also testified in your query by the DISTINCT. For a query like this one, you quick notice the duplicates, but it is more devilish if you have a GROUP BY query, where your SUM or other aggregates may be inflated, but you may not realise that the result is incorrect.

If you would look at the query plan for my query, you would probably see something about Semi-Join, and that is really what the EXISTS subquery is: a semi-join in relational algebra. So it is not different from your query, but it express the intent better, I think.
arkiboys 9,711 Reputation points

2022-05-07T20:36:27.783+00:00

I see.
I will be doing more tests and feed-back

Thank you

Answer 1

Erland Sommarskog 134.7K MVP Volunteer Moderator

I can't say that I understand the rule fully, but I tried to read between the lines. Maybe this is what you are looking for:

SELECT A.V_Number, A.m_name
FROM   (SELECT DISTINCT V_Number, m_name FROM #tblMain) AS A
WHERE  EXISTS (SELECT *
                FROM   #tblMain B
                WHERE  B.V_Number = A.V_Number
                  AND  B.m_name   = A.m_name
                  AND (B.actual IS NULL OR
                       B.Name = 'v DateName' AND weight IS NULL))

arkiboys 9,711 Reputation points

2022-05-07T17:34:41.567+00:00

is it not best to use self join instead?
Thanks
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-05-07T18:18:39.783+00:00

As I said, I had to guess a bit, but for my understanding of the problem, I think this is most natural way of writing the query. Well, if there is a table that defines these (V_number, m_name) combos, I would rather use that table.

I guess your actual data set is larger. You need to try it and see if it meets your needs. If it does not, please give more examples, and explain what should be different.
arkiboys 9,711 Reputation points

2022-05-07T19:52:30.843+00:00
further details for better explanations in-case you may want to update your query accordingly:
the result should return as follows:
In the example, these are rows 12 and 13

V_Number,m_name,party,m_shipment,[Name],estimated,actual,ml_name,Doc_Name,[weight] ------------------------------------------------------------------------------------- 'V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','v DateName','7/30/2021',null,'source','scotland',33.2 'V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','v DateName','7/30/2021',null,'destination','belgium',33.2

Explanation:

This is because, these are the rows for the groups (V_Number, m_name)
which have the [actual] populated for the [Name] column with value (t DateName) populated
and
(
the [actual] column not populated for the [Name] column with value (v DateName) OR the [actual] column not populated for the [Name] column with value (l DateName)
)
and weight is not null

Share via

select -group

tblMain as v1

tblMain as t1

1 additional answer

Explanation:

Your answer