return all rows of outer join - SQL Server Forums

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)

return all rows of outer join

Author

Topic

jeeve01
Starting Member

25 Posts

Posted - 2009-07-01 : 04:08:35

SELECT
a.itemcode,
b.name1,
a.Qty,
SUM(c.QTY) AS SUM_QTY,
SUM(c.AMOUNT) AS SUM_AMOUNT,
SUM(c.NETAMOUNT) AS SUM_NETAMOUNT
FROM a left join c
on a.itemcode = c.ITEMCODE, c
where b.ITEMCODE = c.CODE
and c.DOCDATE BETWEEN '2009/APR/01' AND '2009/JUL/01'
AND a.SHELFCODE = '001'
And a.Qty >= 0
GROUP BY a.itemcode,b.name1,a.Qty
ORDER BY a.itemcode, c.name1
compute count(a.Qty)

for table a, b and c.

i cant return all rows of table a

pls help. any idea

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 04:14:26

move the condition on table c from WHERE clause to the ON clause

Where is table b ? I don't see table b in there ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 04:24:19

This line
on a.itemcode = c.ITEMCODE, c

should be
on a.itemcode = c.ITEMCODE, b <-- Cross join old style.

N 56°04'39.26"
E 12°55'05.63"

jeeve01
Starting Member

25 Posts

Posted - 2009-07-01 : 04:25:14

SELECT
a.itemcode,
b.name1,
a.Qty,
SUM(c.QTY) AS SUM_QTY,
SUM(c.AMOUNT) AS SUM_AMOUNT,
SUM(c.NETAMOUNT) AS SUM_NETAMOUNT
FROM a left join c
on a.itemcode = c.ITEMCODE, b
where b.ITEMCODE = c.CODE
and c.DOCDATE BETWEEN '2009/APR/01' AND '2009/JUL/01'
AND a.SHELFCODE = '001'
And a.Qty >= 0
GROUP BY a.itemcode,b.name1,a.Qty
ORDER BY a.itemcode, c.name1
compute count(a.Qty)

(im sorry. here's the corrected one)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 04:29:36

[code]SELECT a.itemcode,
b.name1,
a.Qty,
SUM(c.QTY) AS SUM_QTY,
SUM(c.AMOUNT) AS SUM_AMOUNT,
SUM(c.NETAMOUNT) AS SUM_NETAMOUNT
FROM a
left join c on a.itemcode = c.ITEMCODE
and c.DOCDATE BETWEEN '2009/APR/01' AND '2009/JUL/01'
left join b on b.ITEMCODE = c.CODE
WHERE a.SHELFCODE = '001'
And a.Qty >= 0
GROUP BY a.itemcode,
b.name1,
a.Qty
ORDER BY a.itemcode,
c.name1
compute count(a.Qty)[/code]

N 56°04'39.26"
E 12°55'05.63"

jeeve01
Starting Member

25 Posts

Posted - 2009-07-01 : 04:39:36

like this one...

SELECT
a.itemcode,
b.name1,
a.Qty,
SUM(c.QTY) AS SUM_QTY,
SUM(c.AMOUNT) AS SUM_AMOUNT,
SUM(c.NETAMOUNT) AS SUM_NETAMOUNT
FROM a left join c
on a.itemcode = c.ITEMCODE,
left join b
on b.ITEMCODE = c.CODE
where c.DOCDATE BETWEEN '2009/APR/01' AND '2009/JUL/01'
AND a.SHELFCODE = '001'
And a.Qty >= 0
GROUP BY a.itemcode,b.name1,a.Qty
ORDER BY a.itemcode, c.name1
compute count(a.Qty)

i have tried it already. it still ends with the same result.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-01 : 04:45:50

You are still referencing a left join inthe where clause! (for table c)

Maybe this:


SELECT
	a.itemcode,
	b.name1,
	a.Qty,
	SUM(c.QTY) AS SUM_QTY,
	SUM(c.AMOUNT) AS SUM_AMOUNT,
	SUM(c.NETAMOUNT) AS SUM_NETAMOUNT
FROM
	a
	left join c on
		a.itemcode = c.ITEMCODE
		AND c.DOCDATE BETWEEN '2009/APR/01' AND '2009/JUL/01'
	left join b on b.ITEMCODE = c.CODE
where
	a.SHELFCODE = '001'
	And a.Qty >= 0
GROUP BY
	a.itemcode
	, b.name1
	, a.Qty
ORDER BY
	a.itemcode
	, c.name1
compute count(a.Qty)

EDIT -- THIS IS EXACTLY THE SAME AS WHAT PESO POSTED EARLIER. DID YOU TRY THAT?
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jeeve01
Starting Member

25 Posts

Posted - 2009-07-01 : 04:47:05

great!!! brother PESO

kh made it too. Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources