|
|
|
date: Fri, 03 Jul 2009 10:00:35 GMT,
group: microsoft.public.sqlserver.server
back
Cartesian with one table
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
date: Fri, 03 Jul 2009 10:00:35 GMT
author: adamscc u53007@uwe
Re: Cartesian with one table
> The SQL that i have added is for the SELECT table and values of:
>
> central_site.site_code,
> central_site.site_name
So you started with a working query and then made some changes? Please post
the original version of the query. I find this query very difficult to
follow due to the mix of old and new style joins, not to mention I have no
knowledge of the table relationships. I find it less likely to miss a
needed join predicate with the ANSI-92 join style, which is one reason why I
personally use the new style exclusively.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"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
>
date: Fri, 3 Jul 2009 10:16:18 -0500
author: Dan Guzman
|
|
