Remove rows from results in SQL Server (mutliple joins)

Question

0.00/5 (No votes)

See more:

Hello, I need to remove duplicate results from a table that is joined to multiple tables. So table A contains invoice information: each work number (worknum) can have multiple invoice ID's, and each invoice ID can have multiple rate codes grouped into categories (i.e. fruit, vegetables, toys, books, candy, drinks). So it looks like this:

TableA:
WorkNum |InvoiceID | ratecodes
1000 |1 | fruit
1000 |1 | vegetables
1000 |2 | books
1000 |3 | drinks
1000 |3 | toys
1002| 4| fruits
1002| 5| books
1002| 7| candy

Now this is linked to other tables to get entered date and customer name by the work number.

TableB:
WorkNum |CustomerName | contact
1000 |CustA | Amy
1000 |CustA | Sue
1002 |CustA | Tom

TableC:
WorkNum |EnteredDate |
1000 |2016-01-01 |
1002 |2016-01-02 |

Now, if one of the rate codes has 'toys' in the work number, I don't want the work number to appear at all.
This is my code:

SQL

Select distinct
tblA.worknum,
tblB.CustomerName,
tblc.entereddate

From TableA tblA
Inner Join TableB tblB on tblA.worknum = tblB.worknum
Inner Join TableC tblC on tblA.worknum = tblc.worknum

Where tblA.worknum = '1000'  
and tblA.ratecodes <> 'toys'

So I get something that looks like this since there are lines without rate code 'toys' (lines 1-4 in table A):

Work Number| Customer | Entered Date
1000 | Cust A | 2016-01-01

I'd like the worknum 1000 to not show up in the results at all because one of the invoice ID's has the rate code 'toys'.

Is there any way to do this? I have many work numbers in there (just want to test my query with one work number).
Thank you!

Posted 17-Jan-16 19:00pm

Member 12270118

Updated 17-Jan-16 20:15pm

v3

Add a Solution

Comments

Sahan2u 18-Jan-16 1:46am

Your question is bit unclear.

If you want to remove only the worknum showing up in the result you can just remove "tblA.worknum," from your select statement. But I hope that is not what you want to do. So need more clarification.

Member 12270118 18-Jan-16 1:51am

Hi Sahan2u,
If I removed tblA.worknum, then I wouldn't be able to get the work number in the results for the other work numbers that don't contain rate code "toys". I want to see all work numbers that do not contain any rate codes "toys" in any of the invoice id's associated with a work number. My query is just a test for on of the invoice numbers to make sure it works since the number of results is so large.

Member 12270118 18-Jan-16 1:54am

Sorry, I meant that I'm using this query to test one of the work numbers to make sure it works as the number of results is very large.

Sahan2u 18-Jan-16 2:11am

OK. Can you tell me what is the expected outcome of the query using above tables and data? just mentioned the data rows that you need to get.

as a sample
Work Number| Customer | Entered Date
1000 | Cust A | 2016-01-01
1000 | Cust A | 2016-01-02

(I just took some data. But mention the exact row details as you expect)

Member 12270118 18-Jan-16 2:18am

I updated tableA.
So what I want to see is this:
Work Number| Customer | Entered Date
1002| CustA | 2016-01-02.
Since work number 1002 in tableA does not have any invoice ID's that have rate code 'toys', I want to see that work number. I don't want to see work number 1000 since invoice ID 3 has rate code 'toys'.

2 solutions

Solution 2

SQL

Select distinct
tblA.worknum,
tblB.CustomerName,
tblc.entereddate
 
From TableA tblA
Inner Join TableB tblB on tblA.worknum = tblB.worknum
Inner Join TableC tblC on tblA.worknum = tblc.worknum
 
where tblA.WorkNum not in (
        select distinct A.WorkNum
        from TableA A
        where A.ratecodes = 'toys')

Posted 17-Jan-16 20:36pm

Sahan2u

Comments

jaket-cp 18-Jan-16 5:16am

Using the "not in" may not be an optimal way of figuring it out.
Have you considered using a left outer join as in solution 1?

Member 12270118 18-Jan-16 10:44am

I tried using your query, and it is taking a very long time (over an hour and still running)...

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)

_Asif_ · Accepted Answer · 2016-01-17T20:22:00

Solution 1

Try this approach,

SQL

DECLARE @TBL TABLE
(
  WorkNum int,
  InvoiceID int, 
  ratecodes varchar(10)
 )
;

INSERT INTO @TBL (WorkNum, InvoiceID, ratecodes)
SELECT 1000, 1, 'fruit'
UNION ALL
SELECT 1000, 1, 'vegetables'
UNION ALL
SELECT 1000, 2, 'toys'
UNION ALL
SELECT 1000, 3, 'drinks'
UNION ALL
SELECT 1000, 3, 'toys'
UNION ALL
SELECT 1002, 4, 'fruit'
;

select *
from   @TBL T
LEFT OUTER JOIN 
(
SELECT WorkNum
FROM @TBL
group by WorkNum, ratecodes
having ratecodes = 'toys'
) TM ON T.WorkNum = TM.WorkNum
WHERE Tm.WorkNum is null

This will show you how you can filter the workitems of type 'toys'. Using above approach you can modify your query like this

SQL

select distinct T.worknum,
tblB.CustomerName,
tblc.entereddate
from   @TBL T
LEFT OUTER JOIN 
(
SELECT WorkNum
FROM @TBL
group by WorkNum, ratecodes
having ratecodes = 'toys'
) TM ON T.WorkNum = TM.WorkNum
Inner Join TableB tblB on T.worknum = tblB.worknum
Inner Join TableC tblC on T.worknum = tblc.worknum
WHERE Tm.WorkNum is null

Posted 17-Jan-16 20:22pm

_Asif_

Comments

Member 12270118 18-Jan-16 2:27am

Hi _Asif_, Thank you for taking a look at my question. Unfortunately, I have around 100+ rate codes (a lot more than the six categories mentioned). I had a feeling that I would need to create a table. How do I get around all those rate codes? and I'm not allowed to create tables in the database so could it be a temporary table? Sorry, I'm brand new to SQL...

_Asif_ 18-Jan-16 2:31am

Let's just stick to your current problem now, we will look at your 100+ rate codes later, did you try executing the above query?

Member 12270118 18-Jan-16 10:42am

I changed it so that it uses a temporary table and works with all the rate codes. It seems to be working, but it's taking a very long time to run the query. Can you think of a way to make the query run faster? Should I create another table?

jaket-cp 18-Jan-16 5:14am

Why do you use group by?
I would say the following would be easier to understand what is going on:
SELECT distinct WorkNum FROM @TBL where ratecodes = 'toys'
Just wondering that is all...

similar as in solution 2

_Asif_ 18-Jan-16 6:37am

Yes, you can do that as well, to me group by let you add more filters if needed later, kind of more adaptive (all you need to add is having filters)

jaket-cp 18-Jan-16 7:10am

Thanks for the reasoning.
Similar to the having, adding additional conditions to the where clause can do the same.

My brain is just wired up a bit differently :)