Solved

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

Posted on 2013-02-05
10
221 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
[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
  • 6
  • 4
10 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 38857458
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
ID: 38860566
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
ID: 38860612
yes that is what it does....
any rows returned means that system had none of its categories selected.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Saroj13
ID: 38860632
its giving the system name, its not returning the count
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38860987
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
 

Author Comment

by:Saroj13
ID: 38862456
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
ID: 38864001
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
ID: 38877538
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
ID: 38881267
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
ID: 38931326
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

627 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