How to exclude customers from showing if they have a specific package
Ok I will try and be as clear as possible. I have two tables one is pp and one is cp. I have one entry field is called classnum in the PP table. The CP table is my customer number. For some reason the code I have now is still pulling the wrong results and I am sure I have something wrong.
The results I am trying to obtain: Show me all customers (custnum) that does not have any packages that have a class of 17.
Each package in this PP table has different classnum.
The results I am getting: It is showing the unique customer numbers but it is showing me ALL unique customer numbers. Currently it is pulling 1558 rows which is my customer count. If the query is correct it should only pull two. As I know for a fact there are two unique customers with a package class of 17.
I have attached the query that I have now.
The results I am trying to obtain: Show me all customers (custnum) that does not have any packages that have a class of 17.
Each package in this PP table has different classnum.
The results I am getting: It is showing the unique customer numbers but it is showing me ALL unique customer numbers. Currently it is pulling 1558 rows which is my customer count. If the query is correct it should only pull two. As I know for a fact there are two unique customers with a package class of 17.
I have attached the query that I have now.
SELECT DISTINCT TOP (100) PERCENT cp.custnum
FROM KC_Freeside.dbo.cust_pkg AS cp INNER JOIN
KC_Freeside.dbo.part_pkg AS pp ON pp.pkgpart = cp.pkgpart
WHERE (cp.cancel IS NULL) AND (pp.classnum <> 17) AND (pp.freq <> '0')
ORDER BY cp.custnum
Last Comment
Just a small addition to the previous solution (no points please):
SELECT DISTINCT cp.custnum
FROM KC_Freeside.dbo.cust_pkg AS cp
LEFT JOIN KC_Freeside.dbo.part_pkg AS pp ON pp.pkgpart = cp.pkgpart AND pp.classnum = 17 AND pp.freq <> '0'
WHERE cp.cancel IS NULL
AND pp.pkgpart IS NULL
ORDER BY cp.custnum
Also, notice how the TOP(100) PERCENT should not be used as it makes no sense. The SQL QO is smart enough to see that you have TOP(100) PERCENT and promptly ignore the ORDER BY clause, so your results will not not sorted.
SELECT DISTINCT cp.custnum
FROM KC_Freeside.dbo.cust_pkg AS cp
LEFT JOIN KC_Freeside.dbo.part_pkg AS pp ON pp.pkgpart = cp.pkgpart AND pp.classnum = 17 AND pp.freq <> '0'
WHERE cp.cancel IS NULL
AND pp.pkgpart IS NULL
ORDER BY cp.custnum
Also, notice how the TOP(100) PERCENT should not be used as it makes no sense. The SQL QO is smart enough to see that you have TOP(100) PERCENT and promptly ignore the ORDER BY clause, so your results will not not sorted.
ASKER
I am currently get 1682 rows now. And the two that shouldn't be in the results are showing up in the results. I also try and remove the TOP 100 PERCENT and it comes right back. I am using SQL server management studio. I am really confused why I can't get this to work.
And the two that shouldn't be in the results are showing up in the results.
Can you post all the records from tables cp and pp of the two results you mentioned?
Can you post all the records from tables cp and pp of the two results you mentioned?
ok from your original statement....
this query will return
those custnums which don't have any classnum 17 's
select distinct custnum
from KC_Freeside.dbo.cust_pkg AS cp
where not exists (select
from KC_Freeside.dbo.part_pkg AS pp
where classnum=17
and cp.custnum=pp.custnum)
order by 1
would you now like to explain what the other two conditions you where trying to apply mean in relation to the problem?
this query will return
those custnums which don't have any classnum 17 's
select distinct custnum
from KC_Freeside.dbo.cust_pkg AS cp
where not exists (select
from KC_Freeside.dbo.part_pkg AS pp
where classnum=17
and cp.custnum=pp.custnum)
order by 1
would you now like to explain what the other two conditions you where trying to apply mean in relation to the problem?
ASKER
I have attached the data in tables to this spreadsheet. Each tab is the data.
Custnum 3654 and 4306 should not show up because they are the only ones with classnum of 17. Let me know if you need something else. Thank you!
CP-PP.xls
Custnum 3654 and 4306 should not show up because they are the only ones with classnum of 17. Let me know if you need something else. Thank you!
CP-PP.xls
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
How do I compensate for that?
what is the intention ?...
if all literal 'Nulls' should be database nulls then that is a trivial correction
vis update table
set [cancel] = nullif([cancel],'Null')
where [cancel] ='Null'
however as i indicated the other table for 3654 at least does have a valid classnum=17 entry...
didn't check the other one...
i'm not sure what datatype you intend for freq since you show a character test <> '0' but at least from
an import point of view that came over as a float...
testing on a smaller sample set is probably advisable initially at least.
if all literal 'Nulls' should be database nulls then that is a trivial correction
vis update table
set [cancel] = nullif([cancel],'Null')
where [cancel] ='Null'
however as i indicated the other table for 3654 at least does have a valid classnum=17 entry...
didn't check the other one...
i'm not sure what datatype you intend for freq since you show a character test <> '0' but at least from
an import point of view that came over as a float...
testing on a smaller sample set is probably advisable initially at least.
>>I also try and remove the TOP 100 PERCENT and it comes right back. I am using SQL server management studio. <<
That is because you are using the Design mode. Don't do that or you will be frustrated to say the least. Use the Query window instead and type in the SQL Statement there.
That is because you are using the Design mode. Don't do that or you will be frustrated to say the least. Use the Query window instead and type in the SQL Statement there.
ASKER
Nulls were a problem. I found a work around.
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Open in new window