Fullsource
asked on
Need help writing an SQL statement that has specific count in WHERE clause
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!
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
<<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.
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.
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