"adamscc" <u53007@uwe> wrote in message news:9883f527d5850@uwe...
> Hi
>
> I have entered some extra fields "site_name and site_code" into some SQL
> but
> when i try to use it to search for one record i recieve a result against
> every site availible in the database.
>
> I have linked the site to the existing data and have referenced it in the
> GROUP BY clause but am having no joy with it just returning on result.
>
> Can anyone help.
>
> This is the SQL
>
> The SQL that i have added is for the SELECT table and values of:
>
>          central_site.site_code,
>          central_site.site_name
>
> SELECT
>          str_wk_occupation.work_header_no ,
>          str_wk_occupation.work_opening_no ,
>          str_wk_occupation.street_work_code ,
>          str_wk_occupation.occupy_status ,
>          str_wk_occupation.actual_start_date ,
>          str_wk_occupation.actual_end_date ,
>          str_wk_occupation.proposed_end_date ,
>          str_wk_occupation.over_run_days ,
>          str_wk_occupation.logged_time ,
>          street_work_type.street_work_name ,
>          street_work_type.charge_group ,
>          organisation_a.organise_name ,
>          sw_header.organise_code ,
>          sw_header.works_ref ,
>          sw_works_status.works_status_name ,
>          sw_notice_header.works_status_code ,
>          street_reinstate.reinstatement_code ,
>          reinstatement.reinstatement_name ,
>          reinstatement.external_reference ,
>          Count ( DISTINCT central_site.site_code ) as street_count,
>          contact.contact_name ,
>          contact.contact_title ,
>          contact.contact_forename ,
>          organisation_b.organise_code ,
>          organisation_b.organise_name,
>          central_site.site_code,
>          central_site.site_name
> FROM
> central_site LEFT OUTER JOIN locality ON central_site.locality_id =
> locality.
> locality_id LEFT OUTER JOIN town ON locality.town_id = town.town_id LEFT
> OUTER JOIN county ON town.county_id = county.county_id,  organisation
> organisation_a LEFT OUTER JOIN contact ON organisation_a.agent_contact_no
> =
> contact.contact_no,
>  sw_header LEFT OUTER JOIN organisation organisation_b ON sw_header.
> highway_org_code = organisation_b.organise_code ,
>           str_wk_occupation ,
>           street_work_type ,
>           sw_works_status ,
>           sw_notice_header ,
>           sw_site ,
>           sw_notice_site ,
>           site ,
>           street_reinstate ,
>           reinstatement
> WHERE
> (str_wk_occupation.occupy_status = 'R' OR str_wk_occupation.occupy_status
> =
> 'D') AND
> street_work_type.charge_group <> 'X' AND
> str_wk_occupation.over_run_days >= 1 AND
> str_wk_occupation.logged_time >= '2009-06-01 00:00:00.00' AND (0 < (
> SELECT
> Count(*)
> FROM
> sw_site           sw_s, sw_notice_site    sw_ns, site
> s_alias,
> ctract_area_group ct_a_grp
> WHERE
> sw_s.work_header_no        = sw_header.work_header_no AND
> sw_s.work_header_no        = sw_ns.work_header_no AND sw_s.work_version_no
> = sw_ns.work_version_no AND
> sw_ns.site_code            = s_alias.site_code AND
> s_alias.contract_area_code = ct_a_grp.contract_area_code AND
> ct_a_grp.work_group_code   = 'ALL' )) AND organisation_a.data_key IN
> (1,2,3,4,
> 5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,100001,100002,
> 100003,150000) AND
> ( street_work_type.street_work_code = str_wk_occupation.street_work_code )
> and
> ( sw_header.organise_code = organisation_a.organise_code ) and
> ( sw_header.work_header_no = str_wk_occupation.work_header_no ) and
> ( sw_notice_header.work_header_no = sw_header.work_header_no ) and
> ( sw_notice_header.work_version_no = sw_header.work_version_no ) and
>
> ( sw_works_status.works_status_code = sw_notice_header.works_status_code )
> and
> ( sw_site.work_header_no = sw_header.work_header_no ) and
> ( sw_notice_site.work_header_no = sw_site.work_header_no ) and
> ( sw_notice_site.work_version_no = sw_site.work_version_no ) and
> ( sw_notice_site.site_number = sw_site.site_number ) and
> ( street_reinstate.site_code = site.site_code ) and
> ( site.site_code = central_site.site_code ) and
> ( reinstatement.reinstatement_code = street_reinstate.reinstatement_code )
> and
> ( sw_notice_header.primary_site_no = sw_site.site_number ) and
> ( ( street_reinstate.reinstate_number = ( SELECT Min ( street_reinstate.
> reinstate_number )
> FROM
> street_reinstate,
> reinstatement WHERE reinstatement.reinstatement_code = street_reinstate.
> reinstatement_code AND
> site.site_code = street_reinstate.site_code AND
> central_site.site_code = street_reinstate.site_code AND
> reinstatement.external_reference = ( SELECT Min( r2.external_reference )
> FROM
> reinstatement r2,
> street_reinstate sr2 WHERE sr2.site_code = street_reinstate.site_code AND
> sr2.reinstatement_code = r2.reinstatement_code ) ) ) and
> (  exists ( SELECT * FROM street_work_depth WHERE
> sw_notice_site.depth_code =
> street_work_depth.depth_code AND street_work_depth.excavation_flag =
> 'Y' ) ) )
>
> GROUP BY
>           central_site.site_code,
>           central_site.site_name,
>           contact.contact_name ,
>           str_wk_occupation.work_header_no ,
>           str_wk_occupation.work_opening_no ,
>           str_wk_occupation.street_work_code ,
>           str_wk_occupation.occupy_status ,
>           str_wk_occupation.actual_start_date ,
>           contact.contact_title ,
>           str_wk_occupation.actual_end_date ,
>           contact.contact_forename ,
>           str_wk_occupation.proposed_end_date ,
>           str_wk_occupation.over_run_days ,
>           str_wk_occupation.logged_time ,
>           street_work_type.street_work_name ,
>           street_work_type.charge_group ,
>           organisation_a.organise_name ,
>           sw_header.organise_code ,
>           sw_header.works_ref ,
>           sw_works_status.works_status_name ,
>           sw_notice_header.works_status_code ,
>           street_reinstate.reinstatement_code ,
>           reinstatement.reinstatement_name ,
>           reinstatement.external_reference ,
>           organisation_b.organise_code ,
>           organisation_b.organise_name
>