Solved

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

Posted on 2013-02-05
10
215 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse this column 6 38
SSIS Standard Template for Reuse by Business Units 12 70
Update one rows based on previous row 5 35
Connect to SQL 2008 r2 server over the Internet 4 79
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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