Query resolve

[davidjennings]

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;

[urtrivedi]

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

[davidjennings]

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

[urtrivedi]

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;

[davidjennings]

Thanks urtrivedi for your reply achieved a solution simular

Thanks for your help
D