Sql join, inner join or group by query issue.

Question

1.00/5 (2 votes)

See more:

I have two tables one is batches
Batches

Batch_Name      ||           Scan_Folder
EMPO-002-091015	||   \\ldatasrv\Demo1\Genric_EmailImptr_Demo\EmailBackupPath\20150910\          
EMPO-003-091015	||   \\ldatasrv\Demo1\Genric_EmailImptr_Demo\EmailBackupPath\20150910\

Invoices

IRN          ||      Customer Name      ||     DTL_Status      || Batch_Name
1111         ||       SAM               ||        NI           || EMPO-002-091015
2222         ||       CLIFF             ||        NI           || EMPO-002-091015
3333         ||       MARY              ||        CO           || EMPO-003-091015
4444         ||       SANJAY            ||        NI           || EMPO-003-091015

I want Batch_Name, Scan_Folder of those batches who has all invoices in NI Status.
Ans: for above table should be
EMPO-002-091015 || \\ldatasrv\Demo1\Genric_EmailImptr_Demo\EmailBackupPath\20150910\
because EMPO-002-091015 has all invoices against it in NI Status.

Please help me for this query i am not able to understand how to initiate it.

Thanks & Regards,
Sunil Mali.

What I have tried:

select b.* from batches b,invoice i
where i.Batch_name=b.Batch_name
and b.DTL....
I am confused...

select distinct batch_name,count(DTL_Status),DTL_Status
from invpagebkp
group by Batch_Name,DTL_Status
having Batch_name='MRAB-0010-011116' order by batch_name

Posted 8-Feb-16 1:29am

sunil mali

Updated 8-Feb-16 1:41am

OriginalGriff

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-02-08T04:08:00

There are a couple of ways you can do this...
This query will get the number of dtl_status for each batch_name

SQL

SELECT Batch_Name, DTL_Status, Count(*) as NIcount from invoices
where DTL_Status = 'NI'
group by Batch_Name, DTL_Status

and this one will get the total number of invoices per batch_name

SQL

SELECT Batch_Name, Count(*) as AllCount from invoices
	group by Batch_Name

You can combine these two queries (I'm using two Common Table Expressions below - see CTE In SQL Server[^]), and join back to your Batches table to get the rest of the data when the count of NI status is the same as the total number of invoices...

SQL

;with t1 as (
	SELECT Batch_Name, DTL_Status, Count(*) as NIcount from invoices
	where DTL_Status = 'NI'
	group by Batch_Name, DTL_Status
)
, t2 as (SELECT Batch_Name, Count(*) as AllCount from invoices
	group by Batch_Name)
select b.Batch_Name, b.Scan_Folder
from t1
join t2 on t1.Batch_Name = t2.Batch_Name
join batches b on t1.Batch_Name = b.Batch_Name
where t1.NICount = t2.AllCount

This can be done even more neatly (if you have SQL Server 2005 or later) by using the OVER[^] clause like this

SQL

;with temp as 
(
	Select Distinct Batch_Name, Count(*) OVER(PARTITION BY Batch_Name) AS Total
		, count(*) OVER(PARTITION BY Batch_Name, DTL_Status) AS NIStatus
	from invoices
)
SELECT b.*
FROM batches b 
join temp on b.Batch_Name = temp.Batch_Name
where Total = NIStatus