Solved

Sql select count query to check atleast one record is coming from each selection.

Posted on 2013-02-05
10
209 Views
Last Modified: 2013-09-27
Hi,

I have webpage
I have 3 System checkboxes. user can select atleast one checkbox.
Corresponding to the system checkboxes, I have category checkboxes. User can select atleast one checkbox.

Ssytem Table
ID, SystemName
1   Sys1
2   Sys2
3   Sys3

Category
1   Cat1
2  Cat2
3  Cat3
4  Cat4

SystemCategory
ID  SystemID  CatID
1    1                1
2     1                2
3    2                 3
4   3                   4

How to write a sql query that tells that atleast one category should be selected for each system?

Suppose if I have selected system 1 and 2, then user needs to select one category from 1/2 and 3.
0
Comment
Question by:Saroj13
  • 6
  • 4
10 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
Comment Utility
if you add the selected systems and categories into the inlists for the cross joined inline tables then running the query will result in either no rows returned ... valid system categories have been selected ..
if rows are returned the returned systemnames have not had a valid category for themselves selected....

 select systemname
    from (
select *
  from (select id as sid ,systemname 
               from systemtable
             where systemname in ('sys1','sys2')
            ) as S
cross join (select id as cid, categoryname  from category
                        where categoryname in ('cat1','cat2')
             ) as c
) as x
left outer join systemcategory as sc
 on x.sid=sc.systemid
and x.cid=sc.catid
group by x.systemname
having count(sc.id)=0
order by 1

Open in new window

0
 

Author Comment

by:Saroj13
Comment Utility
i want something like...

If Systems like Sys1, Sys2 and Sys3 all three are selected, then there should be atleast one category coming from each system... means atleast one category from sys1, atleast one category from sys2, atleast one category from sys3.  If suppose none of the category selected from Sys1, but many categories are selected from each sys2 and sys3 ---> then we need to get count 0 (sql) or error message in c#/javascript
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
yes that is what it does....
any rows returned means that system had none of its categories selected.
0
 

Author Comment

by:Saroj13
Comment Utility
its giving the system name, its not returning the count
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
the count is zero... you only wanted to check where a valid combination hadn't been selected ... those systems returned did not have a valid match with any of the categories selected....

why do you want a count... that sounds more like the sql you would be running after this validation test was passed to actually return some data about the system/category combinations...
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Saroj13
Comment Utility
Let me explain my question agai:

Table1  system;
ID  SysName
1    Sys1
2    sys2
3    Sys3

Table2  Category
ID CatName
1   cat1
2   cat2
3   cat3
4   cat4

Table 3  SystemCategory
ID SysID  catID
1    1         2
2     2        1
3     2         2
4     2          3
5     3         4

Table4 SubCategory
ID CatID SubCatName
1   1        Sub1
2    1        Sub2
3    2       Sub3
4    3       Sub4

If only Sys1 is selected, then catid 2 will appear, atleast one subcategory can be selected
- if sys2 is selected, then cat with id 1,2,3 will appear, and atleast one subcategory can be selected
-if sys3 is selected, then cat with id 4 will appear and atleast one subcategory can be selected
-if sys1, sys2 are selected, then atleast one cat will appear having catid 1,2,3. if suppose atleast one item is selected having catid3, but for sys1, nothing is selected, there should be something selected from catid 2.

if suppose sys1,sys2,sys3 are selected, then atleast one from cat2 and cat4 will be selected.../atleast one from cat2, cat4 cat1/cat3 will be selected
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
no you haven't made it clear , and have introduced another layer/table to your problem...

take a step back and think about what (and why) you are trying to achieve...

the problem ,as you originally stated it, (and still probably) read to me  that you wanted to know if the users had selected a category (list2) which was valid for a system they had selected in there first list...

  my assumption was that if a category was valid for each system then that was sufficient (i'm no longer clear if you now have a requirement that they select an equal number of distinct categories as they have systems?)...

the sql i provided returns any system name for which they haven't select a category that is valid....i.e. has a zero count.
0
 

Author Comment

by:Saroj13
Comment Utility
Hi,

below sql query is not working if the check is for subcategories.

select systemname
    from (
select *
  from (select id as sid ,systemname
               from systemtable
             where systemname in ('sys1','sys2')
            ) as S
cross join (select id as cid, subcategoryname  from subcategory,category
                        where subcategoryname in ('subcat1','subcat2')
                        and subcategory.categoryid=category.categoryid
             ) as c
) as x
left outer join systemcategory as sc
 on x.sid=sc.systemid
and x.cid=sc.catid
group by x.systemname
having count(sc.id)=0
order by 1
0
 

Author Comment

by:Saroj13
Comment Utility
Hi,

How to fix the below query if we need to find out that atleast one sub-category should be selected from each system.

Hi,

below sql query is not working if the check is for subcategories.

select systemname
    from (
select *
  from (select id as sid ,systemname
               from systemtable
             where systemname in ('sys1','sys2')
            ) as S
cross join (select id as cid, subcategoryname  from subcategory,category
                        where subcategoryname in ('subcat1','subcat2')
                        and subcategory.categoryid=category.categoryid
             ) as c
) as x
left outer join systemcategory as sc
 on x.sid=sc.systemid
and x.cid=sc.catid
group by x.systemname
having count(sc.id)=0
order by 1
0
 

Author Comment

by:Saroj13
Comment Utility
Hi,

In the below query, If one of the system is unchecked, then how to query that we dont want
subcategory/category for unchecked system. If sys2 is unchecked, then do the validation...

select systemname
    from (
select *
  from (select id as sid ,systemname
               from systemtable
             where systemname in ('sys1','sys2')
            ) as S
cross join (select id as cid, subcategoryname  from subcategory,category
                        where subcategoryname in ('subcat1','subcat2')
                        and subcategory.categoryid=category.categoryid
             ) as c
) as x
left outer join systemcategory as sc
 on x.sid=sc.systemid
and x.cid=sc.catid
group by x.systemname
having count(sc.id)=0
order by 1
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now