How to Use LeftOuter Join with Where Condition

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 2000 Forums

Transact-SQL (2000)

How to Use LeftOuter Join with Where Condition

Author

Topic

itchocks
Starting Member

44 Posts

Posted - 2003-09-28 : 12:23:42

Hai,

The below is the query

SC010100 is the Stock Table,
ST030100 is the Sales Table,

SC01001 & ST03017 is the Itemcode field
ST03020 is the Sold Quantity field
SC01042 is the Stock Quantity field

select stk.SC01001, stk.SC01002, sum(stk.SC01042) as StkQty,
(select sum(ST030100.ST03020)
from dbo_ST030100 ST030100
where ST030100.ST03017 = stk.SC01001 And ST030100.ST03015 >= @DateStart And ST030100.ST03015 <= @DateEnd) as SoldQty
from dbo_SC010100 stk
Group By stk.SC01001, stk.SC01002
Order by stk.SC01001

I need to show the details as below

ItemCode SoldQty StockQty

I am getting the Sold Qty value correctly where as the Stock Qty value is not correct.

Kindly correct the query if there is anything wrong.

Thank you,
Chock.

Chock

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-28 : 12:53:42

quote:
Kindly correct the query if there is anything wrong.

Any reason why these tables are not named Stock, or Sales? Or the columns as ItemCode and Quantity and SaleDate? If you REALLY want to correct something, correct that first.

You also don't have a LEFT JOIN here, and on the assumption you want to apply the WHERE condition to the right-hand table of a LEFT JOIN, you will end up with an INNER JOIN by design. The following should work but don't ask me to describe it as I really don't know what the hell I'm typing here:

SELECT SC01001, SC01002, sum(SC01042) AS StkQty, sum(ST03020) AS SoldQty
FROM dbo_SC010100 LEFT JOIN ST030100 ON ST03017=SC01001
WHERE ST03015 BETWEEN @DateStart AND @DateEnd OR ST03015 IS NULL
GROUP BY SC01001, SC01002
ORDER BY SC01001

itchocks
Starting Member

44 Posts

Posted - 2003-09-29 : 02:28:01

Hi,

Thank you for your reply. Sorry i written the wrong query in my post. I can't change the name SC01001 to ItemCode because the database is an ERP Product, i am devoloping only the Front end.
Its the production database so i am not using any procedures or updating here. Just retreiving the data and to show the user is what i am doing.

Actually the below is the query i used.

"select stk.SC01001,stk.SC01002,
sum(stk.SC01042) as StkQty,"(select
sum(ST030100.ST03020) from ST030100 where
ST030100.ST03017 = stk.SC01001 And
ST030100.ST03015 >='" & Text2.Text & "' And
"ST030100.ST03015 <='" & Text3.Text & "') as SoldQty
from SC010100 stk Left Outer Join ST030100 sls On
stk.SC01001 = sls.ST03017 Group By stk.SC01001,
stk.SC01002 order by stk.SC01001"

for the above query i am getting 3200 records. its the distinct count of our ItemCode in stock table but the stock value is not correct.

I used your query it shows the Stock and Sales value correctly but what happens is, the record count is only 600. Many of the records are missing. I don't know under what condition it gives the data. Kindly reply me about this.

Thank you very much,
Chock.

Chock

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-29 : 03:29:35

Would make it simpler to make this an SP and pass parameters. Then you could test the query separately from the front end.

you have

select stk.SC01001,
stk.SC01002,
sum(stk.SC01042) as StkQty,
( select sum(ST030100.ST03020)
from ST030100
where ST030100.ST03017 = stk.SC01001
And ST030100.ST03015 >= @var1
And ST030100.ST03015 <= @var2
) as SoldQty
from SC010100 stk
Left Outer Join ST030100 sls
On stk.SC01001 = sls.ST03017
Group By
stk.SC01001,
stk.SC01002
order by stk.SC01001

Is there at most 1 rec in ST030100 for each rec in SC010100? If not then StkQty and SoldQty will contain duplicates.
In fact why do you include ST030100 in the left outer join - it isn't used in the query anywhere.
Looking at the subquery I suspect this is the case as you have a filter included there.

Maybe it should just be

select stk.SC01001,
stk.SC01002,
sum(stk.SC01042) as StkQty,
( select sum(ST030100.ST03020)
from ST030100
where ST030100.ST03017 = stk.SC01001
And ST030100.ST03015 >= @var1
And ST030100.ST03015 <= @var2
) as SoldQty
from SC010100 stk
Group By
stk.SC01001,
stk.SC01002
order by stk.SC01001

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

itchocks
Starting Member

44 Posts

Posted - 2003-09-29 : 05:51:22

Hai,

The below is the query which u written, i modified the date variable instead of yours and now its working fine. I get the correct values.

the below is the one iam using now

select stk.SC01001,stk.SC01002,sum(stk.SC01042) as StkQty,
( select sum(ST030100.ST03020) from ST030100 where
ST030100.ST03017 = stk.SC01001
And ST030100.ST03015 >='" & txtFromDate.Text & "' And
ST030100.ST03015 <='" & txtToDate.Text & "') as SoldQty
from SC010100 stk Group By stk.SC01001,stk.SC01002
order by stk.SC01001

I can't use the Query Analyzer or StoredProcedure. Because i have not rights, because its the production database.

Without using the Left Join I get the output.

Thank you,
Chock.

Chock

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-29 : 09:45:12

quote:
Originally posted by itchocks
I can't use the Query Analyzer or StoredProcedure. Because i have not rights, because its the production database.

Then how do you connect in the first place?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

itchocks
Starting Member

44 Posts

Posted - 2003-09-29 : 12:38:23

quote:
Originally posted by X002548

[quote]Originally posted by itchocks
I can't use the Query Analyzer or StoredProcedure. Because i have not rights, because its the production database.

Then how do you connect in the first place?

The machine where i devolop the application is Win2000 Advanced Server, The database server is Win NT. I didn't install the Ms Sql query analyzer in Win 2000. Just writing query from VB, I am connecting the Database through ODBC. I can work on Server, but as per the instruction, I should not touch the server.

Thank you,

Chock.

Chock

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-29 : 12:40:54

If you install the SQL Client tools on your machine, then you can use Query Analyzer and Enterprise Manager. Client tools are not the server edition, they are just tools. So with these tools on your own machine, you can connect to the server. It is the same thing as connecting from your VB app, it's just better to use the SQL tools when needing to debug SQL queries.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-29 : 13:07:50

Not to mention that everything you're doing now is probably dynamic sql....

Create any stored procedures?

Can you do that with VB?

Don't know...guess you could...but I imagine painful would be the adjective...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

itchocks
Starting Member

44 Posts

Posted - 2003-09-30 : 11:12:57

Hai,

I am having the 3 CDs
MSSql Server 2000 Personal Edition,
MSSql Server 2000 Standard Edition and
MSSql Server 2000 Enterprise Edition

but in the Server is MsSql 7 is installed, its the database server. Can I install the 2000 in the devolopment PC. will it cause any problem if i use 2000 access the tables from the server MsSql7.

If there will not be any problem, then
Which one among the three cd's I have to install to get the QueryAnalyzer and EnterpriseManager

I am not sure whether its possible to write the StoredProcedure in VB and call them. If there is any way tell me.

Thank you,
Chock.

Chock

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-30 : 11:28:35

2000 installs for both 2000 and 7.0...and on the workstation it'll just install the client tools...

Try the Enterprise disc....

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-30 : 12:39:21

Just make sure that you specify client tools only!

Tara

itchocks
Starting Member

44 Posts

Posted - 2003-10-13 : 08:49:50

Hi,

Thank you for your replies. Is it possible to write a stored procedure without going to server from VB. I mean instead of using the Ms Sql, can i do the job with VB 6.

Thank you,
Chock.

Chock

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-13 : 12:25:07

You should write your T-SQL code in Query Analyzer. T-SQL code includes stored procedures, triggers, etc... Query Analyzer is superior to VB when it comes to this type of work.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources