1,105,352 Community Members

Query resolve

Member Avatar
davidjennings
Posting Whiz in Training
218 posts since Sep 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi all I have an sql output query problem I am trying to resolve.

The problem is that I have centres that offer Awards and quals and some centres only offer awards or quals only.

What I need to process is the centres that offer both awards and quals.

tbl.net.qualification contains the data a qual = 0 and awards =1 so in this data output only need pull out centres that have both quals and awards 0 and 1.

Thanks in advance

 select 
        tbl_centre.centre_id,
                    tbl_centre.centre_no,
                    tbl_centre.centre_name,
                    tbl_region.region,
                    tbl_net.qualification,
                    sum(tbl_net.qualification) = count(tbl_net.qualification) as only_does_qualifications,
                    sum(tbl_net.qualification) = 0 as only_does_awards

    from
        tbl_centre

    left join
        tbl_net_centre ON tbl_centre.centre_id = tbl_net_centre.centre_id

    left join
        tbl_net ON tbl_net_centre.ntwrk_cd = tbl_net.network_code

   left join
                    tbl_region ON tbl_centre.region_code = tbl_region.region_ID

    where
                    tbl_centre.reg_status = 'R'
    and
                    tbl_net_centre.net_reg_stts in ('R','P')
    and
                    tbl_region.region_ID ='3'


    group by tbl_centre.centre_id

    having only_does_qualifications - only_does_awards <>0
    limit 1000;
Member Avatar
urtrivedi
Posting Virtuoso
1,897 posts since Dec 2008
Reputation Points: 249 [?]
Q&As Helped to Solve: 408 [?]
Skill Endorsements: 27 [?]
 
0
 

give your sample data also for some records, so that we can understand what data your columns hold,

Member Avatar
davidjennings
Posting Whiz in Training
218 posts since Sep 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

Ok

tbl_centre
Centre_id PK: 1...+ auto increment
centre_no :T0012Z
centre_name Brecon HS
reg_status: R or L registered/lost

tbl_net_centre
nc_id pk:PK: 1...+ auto increment
ntwrk_cd fk:1...+
net_reg_stts:** R or P** registered/pending

tbl_net
ntwrk_cd PK:1...+ auto increment
qualification:** 0 or 1**quals / awards

tbl_region
region_id PK: 1...+ auto increment
region: Southwest

Thanks

D

Member Avatar
urtrivedi
Posting Virtuoso
1,897 posts since Dec 2008
Reputation Points: 249 [?]
Q&As Helped to Solve: 408 [?]
Skill Endorsements: 27 [?]
 
0
 
select 
        tbl_centre.centre_id,
                    tbl_centre.centre_no,
                    tbl_centre.centre_name,
                    tbl_region.region,
                    tbl_net.qualification,
                    sum(CASE WHEN tbl_net.qualification=0 THEN 1 ELSE 0 END ) QUALS, 
                    sum(CASE WHEN tbl_net.qualification=1 THEN 1 ELSE 0 END ) AWARDS, 

    from
        tbl_centre

    left join
        tbl_net_centre ON tbl_centre.centre_id = tbl_net_centre.centre_id

    left join
        tbl_net ON tbl_net_centre.ntwrk_cd = tbl_net.network_code

   left join
                    tbl_region ON tbl_centre.region_code = tbl_region.region_ID

    where
                    tbl_centre.reg_status = 'R'
    and
                    tbl_net_centre.net_reg_stts in ('R','P')
    and
                    tbl_region.region_ID ='3'


    group by tbl_centre.centre_id

    HAVING IFNULL(sum(CASE WHEN tbl_net.qualification=0 THEN 1 ELSE 0 END ),0) >0 AND 
                    IFNULL(Sum(CASE WHEN tbl_net.qualification=1 THEN 1 ELSE 0 END ),0) > 0 
    limit 1000;
Member Avatar
davidjennings
Posting Whiz in Training
218 posts since Sep 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

Thanks urtrivedi for your reply achieved a solution simular

Thanks for your help
D

Question Answered as of 10 Months Ago by urtrivedi
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: