Solved

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

Posted on 2013-02-05
10
211 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
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
 

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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IN with @variable 5 25
select over clause 1 40
SQL compatability in SQL 2016 2 32
Sql Query Datatype 2 19
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

896 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

13 Experts available now in Live!

Get 1:1 Help Now