query sql problem - 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 2005 Forums

Transact-SQL (2005)

query sql problem

Author

Topic

pelolori
Starting Member

15 Posts

Posted - 2010-10-09 : 10:24:32

hi
i've this problem. i want to select alla itemcode from a table ( OITW) that is possibile or not possible to stay in another table ( DLN1)
i have to filter table ( DLN1) by Date, to sum the quantity of itemcode
if the article that is in OITW table is not present in DLN1 i have to extract it the same
this is my query. thanks

SELECT T0.[ItemCode], T0.[OnHand], T0.[IsCommited] as' Ordinato Cliente',T0.[OnOrder] as 'ordinato fornitori', T0.[MinStock] as' Scorta minima', sum(t2.quantity) as 'Quantità Consegnata' FROM OITW T0 INNER JOIN DLN1 T2 ON T0.ItemCode = T2.ItemCode WHERE T0.[WhsCode] = '01' and T2.[DocDate] >= '01.01.2007' and T2.[DocDate] <= '01.12.2010' GROUP BY T0.[ItemCode],T0.[MinStock], T0.[OnHand], T0.[IsCommited], T0.[OnOrder] order by T0.[ItemCode]

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-09 : 13:58:03

i don't understand what you are trying to achieve?

instead of using INNER JOIN you can use OUTER JOIN.

pelolori
Starting Member

15 Posts

Posted - 2010-10-09 : 15:06:02

hi
i have to select all records insert in table 1(OITW), join with table 2(DLN1).
Records insert in table 2(DLN1) must be filtered by date
if i filter these records by date the system give me only the records insert in table 2(DLN1) [using the query insert in the topic] and not all records insert in OITW

i try to use select * FROM OITW T1 LEFT OUTER JOIN DLN1 T2 ON T1.ItemCode = T2.ItemCode WHERE T1.[WhsCode] = '01' and T2.[DocDate] >= '01.01.2007' and T2.[DocDate] <= '01.12.2010'

but the system give me only records in Table 2 (DLN1)

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-10-10 : 13:07:13

quote:

select * FROM OITW T1 LEFT OUTER JOIN DLN1 T2 ON T1.ItemCode = T2.ItemCode WHERE T1.[WhsCode] = '01' and T2.[DocDate] >= '01.01.2007' and T2.[DocDate] <= '01.12.2010'

With limit info, my suggestion now is : replace "where" by "and" in above query to see what result returned.

You should provide sample data and desired output so that people have more info to understand your problem correctly.

pelolori
Starting Member

15 Posts

Posted - 2010-10-11 : 05:02:15

ok thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources