May 1, 2009 at 8:59 am
--First Query
SELECT DISTINCT branch.branch_no,
branch.branch_name,
site.site_no
FROM site,
customer_site,
branch
WHERE site.site_no *= customer_site.site_no
and getdate() between customer_site.start_date and customer_site.end_date
andbranch.branch_no = site.branch_no
and ( site.co_no = 1 )
ORDER BY branch.branch_no ASC, site.site_no ASC
--Second Query
SELECT DISTINCT branch.branch_no,
branch.branch_name,
site.site_no
FROM site
left join customer_site on site.site_no = customer_site.site_no
inner join branch on branch.branch_no = site.branch_no
WHERE
getdate() between customer_site.start_date and customer_site.end_date
and ( site.co_no = 1 )
ORDER BY branch.branch_no ASC, site.site_no ASC
Can any one help me why both query not returning same count?
Thanks in advance .
May 1, 2009 at 10:51 pm
Use Inner Join between Site and Customer_site tables...
because non ansi join act as inner join
May 2, 2009 at 5:19 am
Hi
Looks equal for me but I'm no pro in this non-ansi syntax. Could you please explain more detailed what's the difference in the returned data? Some test data would be great.
BTW: Please use [ code ] and [ /code ] (without the spaces) around your SQL statements for better readability.
Greets
Flo
May 2, 2009 at 7:33 am
Part of the filter criteria needs to move up to the join criteria. Try this query and compare it to the results of the original.
SELECT DISTINCT
branch.branch_no,
branch.branch_name,
site.site_no
FROM
site
left join customer_site
on (site.site_no = customer_site.site_no
and site.co_no = 1)
inner join branch
on (branch.branch_no = site.branch_no )
WHERE
getdate() between customer_site.start_date and customer_site.end_date
ORDER BY
branch.branch_no ASC,
site.site_no ASC
And if that doesn't work, try this:
SELECT DISTINCT
branch.branch_no,
branch.branch_name,
site.site_no
FROM
site
left join customer_site
on (site.site_no = customer_site.site_no
and site.co_no = 1
and getdate() between customer_site.start_date and customer_site.end_date)
inner join branch
on (branch.branch_no = site.branch_no )
ORDER BY
branch.branch_no ASC,
site.site_no ASC
May 2, 2009 at 8:22 pm
Heh... it could simply be that the non-Ansi join was returning the incorrect row count to begin with. From BOL...
In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, [font="Arial Black"]this syntax results in an ambiguous query that can be interpreted in more than one way[/font]. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2009 at 12:27 am
Jeff Moden (5/2/2009)
Heh... it could simply be that the non-Ansi join was returning the incorrect row count to begin with. From BOL...In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, [font="Arial Black"]this syntax results in an ambiguous query that can be interpreted in more than one way[/font]. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity.
Having had to help our PeopleSoft developers with converting old style outer joins to ANSI style outer joins, it was a requirement that the queries returned the same result set. This sometimes meant, appropriately, that the entire where clause moved into the join criteria. The developers understood the data and the system and with very few exceptions was the correct procedure. It really comes down to knowing the data and the application.
May 3, 2009 at 11:46 pm
Thnaks second solution will work ...
Thanks again ...
April 13, 2012 at 10:16 am
look at this link to understand Conversion
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply