inner join to outer join problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

inner join to outer join problem

Author

Topic

emustafa
Starting Member

5 Posts

Posted - 2008-03-01 : 12:27:37

hello, i am running mysql server 5 and i have sql syntax like this:
select
sales.customerid as cid,
name,
count(saleid)
from
sales
inner join
customers
on
customers.customerid=sales.customerid
group by
sales.customerid
order by
sales.customerid;
it works fine and speedy. but when i change inner join to right join, in order to get all customers even there is no sale, my server locks up. note: there is about 10000 customers and 15000 sales.
what can be the problem?
thanks,

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-01 : 13:52:43

There are some known issues (bugs) with RIGHT joins per msdn (at least with SQL 2000)

change to left join from Customers to Sales


select sales.customerid as cid,
	  [name],
	  Sum(isnull(saleid,0)) as SalesIDCount
from Customer LEFT join Sales
on customers.customerid=sales.customerid
group by sales.customerid,[name]
order by sales.customerid;

Poor planning on your part does not constitute an emergency on my part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-01 : 16:50:28

Dataguru, please provide some link(s) to ms knowledge base about this...

E 12°55'05.25"
N 56°04'39.16"

emustafa
Starting Member

5 Posts

Posted - 2008-03-01 : 17:19:54

hello thank you for your answer. but the problem occurs when i try to make outer join for customers. both "select ... from Customer LEFT join Sales on ..." and "select ... from Sales RIGHT join Customer on ..." cause the problem. there must be logical error i guess, but what?

quote:
Originally posted by dataguru1971

There are some known issues (bugs) with RIGHT joins per msdn (at least with SQL 2000)

change to left join from Customers to Sales

select sales.customerid as cid,
[name],
Sum(isnull(saleid,0)) as SalesIDCount
from Customer LEFT join Sales
on customers.customerid=sales.customerid
group by sales.customerid,[name]
order by sales.customerid;

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-01 : 17:31:36

quote:
Originally posted by Peso

Dataguru, please provide some link(s) to ms knowledge base about this...

E 12°55'05.25"
N 56°04'39.16"

My bad...looking through my notes on this it was a bug.Our server that I encountered the error on had not yet been updated with the hotfix.

http://support.microsoft.com/kb/892451/en-us

this was the issue I recalled encountering...but I ended up rewriting the procedure anyway as the offensive right join in this case wasn't needed anyway.

sorry for the confusion.

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-01 : 17:33:17

quote:
Originally posted by emustafa

hello thank you for your answer. but the problem occurs when i try to make outer join for customers. both "select ... from Customer LEFT join Sales on ..." and "select ... from Sales RIGHT join Customer on ..." cause the problem. there must be logical error i guess, but what?
quote:
Originally posted by dataguru1971

There are some known issues (bugs) with RIGHT joins per msdn (at least with SQL 2000)

change to left join from Customers to Sales

select sales.customerid as cid,
[name],
Sum(isnull(saleid,0)) as SalesIDCount
from Customer LEFT join Sales
on customers.customerid=sales.customerid
group by sales.customerid,[name]
order by sales.customerid;

Poor planning on your part does not constitute an emergency on my part.

Well do you want all customers, all sales, or all of both?

You may need a cross apply
http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

Poor planning on your part does not constitute an emergency on my part.

emustafa
Starting Member

5 Posts

Posted - 2008-03-01 : 23:42:33

quote:

Well do you want all customers, all sales, or all of both?

i need all customers with sale-counts.
thank you,

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-02 : 00:57:13

[code]
select customers.customerid as cid,name,count(saleid)
from customers LEFT join sales
on customers.customerid=sales.customerid
group by customers.customerid,[name]
order by customers.customerid;
[/code]

Locks up your server with only 10,000 customers?

inner join works because it doesn't matter which customerID you use...if you do left join from customers to sales..the above should work...

if you get an error..what is it?

Poor planning on your part does not constitute an emergency on my part.

emustafa
Starting Member

5 Posts

Posted - 2008-03-02 : 05:31:02

i copied tables to an access file. then, left and inner join worked properly. my server causes the problem. thank you for all,

quote:
Originally posted by dataguru1971

select customers.customerid as cid,name,count(saleid)
from customers LEFT join sales
on customers.customerid=sales.customerid
group by customers.customerid,[name]
order by customers.customerid;

Locks up your server with only 10,000 customers?

inner join works because it doesn't matter which customerID you use...if you do left join from customers to sales..the above should work...

if you get an error..what is it?

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-02 : 09:37:14

I think your answer remains unsolved. Just because it worked in Access doesn't mean your server caused the problem...

Poor planning on your part does not constitute an emergency on my part.

emustafa
Starting Member

5 Posts

Posted - 2008-03-03 : 04:23:57

quote:
Originally posted by dataguru1971

I think your answer remains unsolved. Just because it worked in Access doesn't mean your server caused the problem...

Poor planning on your part does not constitute an emergency on my part.

actually, yes. i have deleted some records from mysql server. there are left 30 customers and 30 sales. the query worked. but it is very slow, takes 2-3 seconds. i am using mysql 5.0.9 version. i wonder if there is a way to do it faster? or it is time to switch to mssql?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources