Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

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

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
Saroj13
Asked:
Saroj13
  • 6
  • 4
1 Solution
 
LowfatspreadCommented:
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
 
Saroj13Author Commented:
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
 
LowfatspreadCommented:
yes that is what it does....
any rows returned means that system had none of its categories selected.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Saroj13Author Commented:
its giving the system name, its not returning the count
0
 
LowfatspreadCommented:
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
 
Saroj13Author Commented:
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
 
LowfatspreadCommented:
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
 
Saroj13Author Commented:
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
 
Saroj13Author Commented:
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
 
Saroj13Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now