?
Solved

Need help writing an SQL statement that has specific count in WHERE clause

Posted on 2009-07-01
6
Medium Priority
?
260 Views
Last Modified: 2012-05-07
Greetings i am having trouble getting this to work...
What i need is to return the results from tbl_A where when joined to tbl_B the information is NOT duplicated 6 times.

Basically here is my problem. i have an items location table (tbl_B) with fields like UID (Primary Key), CID (category ID), InternalID (item Id), Priority (order of Items)....

Lets say i  have 6 items in the category with a CID of '564' when i write an SQL table Join with no WHERE statement i get 6 results just for that CID, which in turn tells me there are 6 items in that category. What i need to find is the categories without 6 items in them. Apparently the client wants only 6 items in each category. no more no less... Is it possible to get an SQL statement to find only those categories? Hope this make sense, and thanks in advance for any help!
SELECT * FROM tbl_A 
    LEFT JOIN tbl_B ON tbl_B.CID = A.CID 
 
Need to write WHERE duplicates != 6

Open in new window

0
Comment
Question by:Fullsource
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 4

Assisted Solution

by:j_s_kelley
j_s_kelley earned 400 total points
ID: 24755173
Try this to get you started.  Haven't checked syntax but this is close to what you want I think.
SELECT tbl_B.CID FROM tbl_A 
    LEFT JOIN tbl_B ON tbl_B.CID = A.CID
Group by tbl_B.CID
Having Count(tbl_B.CID) <> 6

Open in new window

0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 24755181
Hello Fullsource,

SELECT *
FROM tbl_A
WHERE CID NOT IN (SELECT CID FROM tbl_B GROUP BY CID HAVING COUNT(*) = 6 )

Regards,

Aneesh
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24755293
I'd suggest...

select distinct A.CID
from tbl_A  A
        inner join tbl_B B
        on A.CID = B.CID
group by A.CID
having count(distinct B.UID) < = 6
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 1200 total points
ID: 24755337
select distinct A.*
from tbl_A A
inner join
(
select distinct CID from tbl_B group by CID
having count(distinct UID) < = 6
) B
on A.CID = B.CID
0
 
LVL 4

Expert Comment

by:j_s_kelley
ID: 24755518
Be careful.  Racimo's answer has a less than or equal.  This is not what you stated.  You wanted answers that were not exactly 6.  His answer will give you CID's that have 6 rows in B, or 5 rows, or 4 rows, etc....

Also, be careful using a Not In clause because it is very inefficient.  I don't care about the points so much, you just need to know that the accepted solution doesn't answer the question as stated.  I also am not trying to be difficult but my nature is to be as complete as possible.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24755664
<<Be careful.  Racimo's answer has a less than or equal.  This is not what you stated.  You wanted answers that were not exactly 6.  His answer will give you CID's that have 6 rows in B, or 5 rows, or 4 rows, etc....>>
Not totlly clear I realize by your comment...but the questionner also specified *Apparently the client wants only 6 items in each category. no more no less* which is why I considered that any value below or equal 6 is necessary.  In that case, the query provided *does* answer the question.  

0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question